Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Distinguishing empty cells (Excel 2002)

    How can I distinguish between a cell having a zero value and being empty? How can I distinguish between a cell containing a zero value, and a cell containing a formula who's result is zero? Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Distinguishing empty cells (Excel 2002)

    Since you ask in the VB / VBA forum: to test if a cell is empty, use <code>If Range("A1") = "" Then</code>. To test if a cell has a formula, use <code>If Range("A1").HasFormula Then</code>. Example:

    Sub Test(aCell As Range)
    If aCell = "" Then
    MsgBox "Cell is empty"
    ElseIf aCell = 0 Then
    If aCell.HasFormula Then
    MsgBox "Cell has formula resulting in 0"
    Else
    MsgBox "Cell contains value 0"
    End If
    Else
    MsgBox "Cell is not empty or 0"
    End If
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinguishing empty cells (Excel 2002)

    Thank you. I used Left(ActiveCell.Formula,1)= "=", but HasFormula is much better.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Distinguishing empty cells (Excel 2002)

    I don't know if this is an issue for Andrew, but if acell contains a null string or has a formula which results in a null string, your sub will list the cell as "empty".

    I prefer to test for "blank" using IsEmpty(acell) [the VB equivalent to isblank]. This will distinguish a truly empty cell vs a cell whose value is the null string.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Distinguishing empty cells (Excel 2002)

    Andrew

    I know this is the VB/VBA forum and I am not sure what application you are running but a very unknown shortcut to show formulas in cells is what I call the "funny key" as no one has ever told me what its name is.

    Press CTRL+ "Funny key" as depicted on the attached image. ( sorry, this was the only image I had available, but is the location of the key I am talking about)

    This shows all cells which contain formulas
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Distinguishing empty cells (Excel 2002)

    Jerry,

    According to the online help, it's Ctrl+` instead of Shift+`:
    <hr>CTRL+` (single left quotation mark)
    Alternate between displaying cell values and displaying formulas.<hr>
    The key on my US style keyboard displays the "accent grave" ` and the "tilda" ~.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Distinguishing empty cells (Excel 2002)

    Good point.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Distinguishing empty cells (Excel 2002)

    Thanks Hans

    I realised after I posted it. I was editing the text as you were replying. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Jerry
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •