NULL cell value = 0 ?? --for VBA

Select Case Cells(2 , 7)

     Case 0
         zwch = 5

     Case ""
         zwch = 5

     Case 1 To 1999

End Select



My question:

cell(2,7) is  NULL ( nothing in it ) , should I write program as  -- case ""
?
I tested this, found that even without this sentence ( case ""), the
programe runs well and the programme look on NULL cell as 0


What's your opionion ?


0
Martin
9/13/2004 11:37:45 AM
excel 39879 articles. 2 followers. Follow

1 Replies
329 Views

Similar Articles

[PageSpeed] 23

VBA will try to cast the result of Cells(2, 7) (which defaults to 
Cells(2, 7).Value) as the same type as the Case argument. So an empty 
cell evaluated as a string is cast to a null string "", and evaluated as 
a number is cast to 0.

Since you want empty, 0 and the null string to result in the same value 
for zwch, you can use either. If you wanted them to evaluate 
differently, you could do something like:

    With ActiveCell
        If IsEmpty(.Value) Then
            zwch = "Empty"
        Else
            Select Case ActiveCell.Value
                Case 0
                    zwch = "Zero"
                Case 1 - 1999
                    zwch = "Positive, less than 1000"
                Case Else
                    zwch = "Something else"
            End Select
        End If
    End With
    MsgBox zwch

In article <eg90SYYmEHA.712@TK2MSFTNGP09.phx.gbl>,
 "Martin" <lajitong888@21cn.com> wrote:

> Select Case Cells(2 , 7)
> 
>      Case 0
>          zwch = 5
> 
>      Case ""
>          zwch = 5
> 
>      Case 1 To 1999
> 
> End Select
> 
> 
> 
> My question:
> 
> cell(2,7) is  NULL ( nothing in it ) , should I write program as  -- case ""
> ?
> I tested this, found that even without this sentence ( case ""), the
> programe runs well and the programme look on NULL cell as 0
> 
> 
> What's your opionion ?
0
jemcgimpsey (6723)
9/13/2004 12:21:16 PM
Reply:

Similar Artilces:

Great Plains 8.0 on a Domain Controller
Has anyone ever experienced any problems installing Great Plains 8.0 Standard on a Windows 2003 Server Domain Controller? The site has a very low number of users (8) and they want to do this. Any issues with GP support after the fact? Thanks, Kevin Cristiano Nope.... I have a GP MSSQL system running on SBS 2003, which is a domain controller, with exchange, isa and other things. Just make sure you have enough hardware and resources MLF "KJC" wrote: > Has anyone ever experienced any problems installing Great Plains 8.0 > Standard on a Windows 2003 Server Domain Cont...

Need invisible/shaded/highlighted cell
I'm thinking what I would like to do can *probably* be done in VBA, but I'd like to use the formula in the cell. If it can be done. I have three columns: DOW DATE BILL DTE --- -------- -------- Wed 02/01/06 02/03/06 Thu 02/02/06 02/06/06 Fri 02/03/06 02/07/06 Sat 02/04/06 02/06/06 Sun 02/05/06 02/07/06 Mon 02/06/06 02/08/06 Tue 02/07/06 02/09/06 Wed 02/08/06 02/10/06 . . . . The date col contains a formula to add 1 to the cell above. The day of week col uses the date from the date column. And the bill date col adds 4 days to the date col if it falls on a "Thu...

Transparent bitmap in FlexGrid cell?
I have a bitmap in the resource (IDB_PICTURE). I need to display it on a FlexGrid's cell with transparent background. Could someone show me how? This is the only function available to put a picture on a cell CFlexGrid::putref_CellPicture(LPDISPATCH) Thank you. ...

BP 3.0 pre-installation question
I am very embarrassed to ask this, but I have not found the correct way to do set up the Business Portal domain user account. In short, I have a domain called MYDOM. I have an SBS 2003 machine called PDC I have the Business Portal machine called TESTBED (running Server 2003). I hav no problems creating the domain user account "bportal", but how do I give that account admin privileges on specirfically on TESTBED? It is not like XP pro where I can add the domain account to te local users accounts and give it admin rights. It seems that this should not be an utter brick wall, ...

linking cells #3
I have a calendar created in Excel - each sheet is a new month that contains the following information: the last week of the previous month, the current month, and the first week of the next month. I would like to link the cells from the "overlapping" weeks, but I do not want the "0" to show up in the cells. Is there any way to prevent this? Thanks in advance! Paula Either choose not to show zero values through Tools>Options>View or trap the zero and turn to a blank-looking character. =IF(sheet1!A1="","",sheet1!A1) Gord Dibben Excel M...

a little help with finding and associating cells
ok so here is my dilemma, I need a formula that will look at a cell value on one work sheet, check for the same value on another work sheet in a defined column, and insert a coresponding value from another column...... is that even remotely possible??? any help would be greatly appreciated.... -- rcarrollct ------------------------------------------------------------------------ rcarrollct's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24931 View this thread: http://www.excelforum.com/showthread.php?threadid=384671 It's called VLOOKUP. http://www.offi...

CRM 3.0 not routing incoming emails
The router logs on the Exchange server show this error: Event Type: Error Event Source: MSCRMExRouter Event Category: None Event ID: 0 Date: 10/10/2006 Time: 3:48:39 PM User: N/A Computer: TARARI-EXCH Description: Microsoft.Crm.Tools.ExchangeConnectorService.ExchangeSinkServiceException: Verifying before delivery message: RE: Tarari Issue in mailbox: crmemail failed (System.Web.Services.Protocols.SoapException: Server was unable to process request. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream,...

insert symbols in text cell
i would to insert "-" to the text cell format, how to do? example: 123456 change to 123-456 -- SelinaT ------------------------------------------------------------------------ SelinaT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33675 View this thread: http://www.excelforum.com/showthread.php?threadid=534451 Assuming the data posted is representative (6 digits, with dash to be inserted in centre) and running in A1 down Try in B1: =LEFT(A1,3)&"-"&RIGHT(A1,3) Copy down Then copy col B, and overwrite col A with Paste special &g...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

minimum cell value
Hi, I am trying to set up a cell so it has a minimum value. The current formula is: =a1/a2*5 which gives the answer 0 but i need to show a minimum value of 1 is this possible? Hi, Doug, Try: =Max(a1/a2*5,1) --- Regards, Norman "Doug Bell" <Doug Bell@discussions.microsoft.com> wrote in message news:8766BC4D-D3CD-4B4F-AF7B-FD145DE56205@microsoft.com... > Hi, > > I am trying to set up a cell so it has a minimum value. > > The current formula is: =a1/a2*5 which gives the answer 0 but i need to > show > a minimum value of 1 is this possible? > ...

I can not see all contents in one cell. Help me pls!
Please help me! By default when I write some text in cell and the text is longer then cell's width, I can not see his contents in next cells. But in one excel file when I sellect all cells in worksheet and then draging them it can no longer seen all contents in one cell. What I should to do? Thank you in advance! If you have wraptext turned on (format|cells|alignment tab), then the cell will expand its rowheight to show you all the data. (unless you use merged cells or have adjusted the rowheight manually). If you have wraptext turned off, then the text will overflow to the cell to ...

Filter List for Linked Values
I have a list of Numbers. In that list some Cells are: - Linked Values [ e.g. ='[Sales.xls]Sheet1'!K61) ] - Forumlas [ e.g. =1802+254+356 or =sum(A2:B32) ] - Manually Entered I want to filter the list for linked values. Is this possible? Hi not without VBA (using a user defined function) and a helper column >-----Original Message----- >I have a list of Numbers. In that list some Cells are: > > - Linked Values [ e.g. ='[Sales.xls]Sheet1'!K61) ] > - Forumlas [ e.g. =1802+254+356 or =sum(A2:B32) ] > - Manually Entered > > I want to filter the l...

format to calculation cell
I have a spreadsheet which has a weight column. This is exported as 25kg, 1000kg, 10x2kg from another program. I am trying to use this weight column in a calculation. I have inserted a new column and copied the information over and then used the find/replace to remove the KG to use in the calc cell. This works fine for the 25 / 1000 etc, but the 10x2 will not caculate. Is there a way that when i paste the cells that i can get it to change to 20 automatically. ( i presume that the x will need to be a * ?) thanks. -- mdma --------------------------------------------------------------...

"Diagonal" cells
Hi. I'm doing a tricky poor-man's-Illustrator project with Excel 2007. I'm trying to create the impression of a large cell that's been split in half diagonally (with a diagonal ascending from bottom left to top right). I've created a 2 x 2 grid of cells and put a diagonal border through the lower left and top right cells and removed the vertical, internal borders. Looks great so far. In addition, I'd like to place text in the upper left and lower right cells. Ideally, this text would be wider than the upper left or lower right cell and would flow over into the adja...

import hyperterminal value into excel
How do I import a hyperterminal value into an excel cell? ...

Line chart, value labeling
I like to change line graph settings so that only one value will appea on the line (not all the values). I right clicked on the point on the line, selected format data series selected Data Labels tab, checked box Show Values. and then click ok. All the data values show up on the line. But, I like to see only on specific value. How do we do that -- Message posted from http://www.ExcelForum.com Hi, You need to select the line series and then select a single point before displaying the format dialog and enabling data labels. Cheers Andy NYBoy < wrote: > I like to change line graph s...

Formatting cell for state abbreviations
I am unable to format cell to accept ME, the abbreviation for Maine. It continues to revert to a lowercase "E" as in "Me" while other states such as NH, VA and LA maintain their uppercase second letter. What am I doing wrong? Thank you -- LovesArt247 This is a AutoCorrect item for people who mistype the word "Me" (as in me myself) Tools | Autocorrect; locate and remove this entry -- remember it will effect all Office apps OR: after Excel gives you Me, use CTRL+Z to undo best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme rem...

Excel 2003, when I click on a particular cell it gets deselected
While using Excel 2003, when I click on a particular cell, within 30 seconds, that particular cell gets deselected followed by the workbook. So each time I need to click either the cell or workbook to enter data in that particular sheet. There could be event code that is being fired. Does this happen if you open the workbook with macros disabled? HTH, Bernie MS Excel MVP "towinwin" <towinwin@discussions.microsoft.com> wrote in message news:B7B87AFB-27AC-4235-9AE3-5E7A46310AF8@microsoft.com... > While using Excel 2003, when I click on a particular cell, within 30 se...

Migrate Contact Address Data from CRM 1.2 to 3.0
We are upgrading from CRM 1.2 to CRM 3.0. In order to migrate the existing data, we are using the CRM web services (in "../crmservice.asmx"). CRM 1.2 stores the contact data in the ContactBase table; I create a data source, create an instance of the "contact" object from the CRM web service, set each of the contact properties from the field values in the DataRows of the DataSet source, and then use the contact object's Create() method to add each contact to the CRM 3.0 database -- all of this works very well. However, CRM 1.2 stores the contact's address d...

how do I highlite text within a cell (specific characters)
I am trying to high light specific characters within a cell. (similiar to the way text can be high lighted in word. Can this be done in Excell. I dont want to change the colour of the cell or the colour of the text - but do want to high light specific parts of the text with in a cell Just select that part of the text and format as per your requirement by going to cells > format > font. Mangesh "tim" <tim@discussions.microsoft.com> wrote in message news:A9781298-1301-4FE1-9901-9D1C03504504@microsoft.com... > I am trying to high light specific characters within a ...

creating a macro that will paste a value and then move
Please, help! I am trying to recreate a macro that opens a workbook, goes to Sheet 1, pastes clipboard info into A1, then moves to B1, then saves and closes the file. I know how to make the file automatic and how to get it to open and close (Auto_Open), but I cannot remember how to get the macro to move from cell to cell (left-to right). What happens is that when I do a recorded macro and then try to rerun it; it will paste into A1 and then will tab over to B1. Then upon the next time that I call up the spreadsheet, it will even paste to B1, but will not move further from that spot. I then...

Getting value from 2 cols right, 1 row down
Hi, I guess this is a simple question but I couldn't figure out how to ge around it. Code ------------------- A B C D E F 1 Worker 100 100 100 100 2 110 110 3 Worker 120 120 120 4 130 130 13 ------------------- Sheet above is a work schedule and 100, 110, 120 and 130 are jus predefined work shifts. Row 1 is a morning shift row and Row 2 is night shift row for the first worker. Now what I want to do is to create another sheet with workers names t m...

Cell colors
In Format Cells (Excel 2008), there are 40 colors in the top five rows and 16 more colors in the bottom two rows. I see how to change the colors in the top rows (Preferences > Colors), but how do you change the ones in the bottom rows? Charles ...

Weird problem -- formulas get lost but their values stay in place
I have a client who uses Excel files that link out to other excel files. The formulas in the "main" file calculate from data in the linked files. So, the formula is in the main file. When they close the main file and re-open it, the formulas are gone, but the last calculated value remains. Does anyone have an idea why this would happen? Thanks, Bill One way might be because a "Before Save" or "Before Close" Macro is doing a Copy > Paste special > Values........ Vaya con Dios, Chuck, CABGx3 "Bill" <bdotson@gmail.com> wrote in message...

Text in VBA
Hello, I'd like, if possible, in VBA to have an inputbox that will allow me to write a text in J1 if B1 has value but J1 is empty. Is that possible? Hi how should this inputbox be invoked? -- Regards Frank Kabel Frankfurt, Germany "Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag news:A7813AAF-1A10-4F19-86D8-80F25F89434F@microsoft.com... > Hello, > > I'd like, if possible, in VBA to have an inputbox that will allow me to > write a text in J1 if B1 has value but J1 is empty. > Is that possible? > This inputbox should be invoked if...