Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post

    When is an empty cell not an empty cell?

    No, I'm not just waxing philosophical.

    In the attached workbook I need to replace blank cells with zeros. The workbook in question is an example of data I get from a laboratory analysis package; I'll be getting data like this every day.

    I've got this code to do the replacement:

    Sub FillEmptyBlankCellWithValue()

    'Fill an empty or blank cell in selection with zero

    Dim cell As Range

    On Error Resume Next

    'Test for empty cell. If empty, fill cell with value given

    For Each cell In Selection
    If IsEmpty(cell) Then
    cell.Value = "0.00"
    End If
    Next
    End Sub

    When I select the range E3E5, it's not replacing the blanks with zero, though the code works great on a newly created blank workbook. This implies that the cells aren't blank, yet I can't find a way to determine what value IS there. How can I figure out what IS there so I can edit the code?
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    Never mind! There's a space in those cells. Off to edit my code...

  3. #3
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    OK, I was wrong. The value in those cells seems to be a space followed by a carriage return (when I copy them from Excel and paste them into Word, I get a space followed by the paragraph symbol). I've tried amending the code to find the space and the carriage return, to no avail. Can anyone please help me out?

    Thanks...

  4. #4
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    If you want them to have a zero in them by default, set the format of the cells to type "number". If nothing is in them, it will show a zero on the screen.

    But I'm not sure if there is an actual zero in the cells, or if it simply displays a zero.

  5. #5
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    Jim,

    Didn't work when I tried it. I formatted them as Number and they still display as blank.

  6. #6
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    I'm back to being stumped. I can't figure out what is in those cells. If I Paste Special-->Unformatted text into Word, I get nothing. If I do a normal Paste of just one cell into Word, I get a space followed by a hard return. If I paste several cells, it tells me there are spaces in those cells, and indeed if I do a Find within Word it tells me all those cells have spaces in them. However, doing the same Find in Excel finds no results at all.

    Can anyone tell me how to figure out what's in the "blank" cells, and then how to replace those values with zeros?

  7. #7
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,396
    Thanks
    445
    Thanked 404 Times in 376 Posts
    Quote Originally Posted by Beej View Post
    Jim,

    Didn't work when I tried it. I formatted them as Number and they still display as blank.
    I wasn't absolutely sure, but I figured that it was worth a try.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Beej View Post
    I'm back to being stumped. I can't figure out what is in those cells. If I Paste Special-->Unformatted text into Word, I get nothing. If I do a normal Paste of just one cell into Word, I get a space followed by a hard return. If I paste several cells, it tells me there are spaces in those cells, and indeed if I do a Find within Word it tells me all those cells have spaces in them. However, doing the same Find in Excel finds no results at all.

    Can anyone tell me how to figure out what's in the "blank" cells, and then how to replace those values with zeros?
    I suspect they might contain zero-length strings.

    If you amend your code to say something like
    If cell.value = "" then cell.value = "0.00"

    does this work?

  9. The Following User Says Thank You to jeremybarker For This Useful Post:

    Beej (2013-03-06)

  10. #9
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    That did the trick! THANKS.

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Beej,

    It could be a character < ascii 64! I had this problem the other day when I downloaded something from the web. All the entries in Col A were preceded by what appeared to be two spaces but wern't! Try this code to check.
    Code:
    Option Explicit
    
    Sub FindNonAscii()
    
       Dim iCntr As Integer
       
       With ActiveCell
           If Len(.Value) > 0 Then
             For iCntr = 1 To Len(.Value)
                If Asc(Mid(.Value, iCntr, 1)) < 63 And _
                   Asc(Mid(.Value, iCntr, 1)) <> 32 Then
                MsgBox "Cell: " & .Address(, , xlA1) & _
                   "contains a character with an ASCII value < 64 at position: " & _
                   Format(iCntr, "###") & vbCrLf & _
                   "The value of the character is: " & Asc(Mid(.Value, iCntr, 1)), vbOKOnly + vbInformation, _
                   "Non Visible Character Alert"
                End If
             Next iCntr
           End If
       End With
       
    End Sub
    Place your cursor in a cell in question then hit Alt+F8 and double click on FindNonASCII.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post
    RG, Thanks for the response. Jeremybarker nailed it; they were zero-length strings. However, I've now found another new wrinkle, so look for another post going up in a few minutes.

    Really, you folks are such a resource!

Posting Permissions

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