• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

"" not treated as empty

The below code works as long as the file is emptied by using the Delete Key..  If I go in and hit the spacebar to empty it ignores the value = "".  

So for example hte

  If .Offset(0, -1).Value = "" Then

can appear empty, but the script does not treat it as such.

Sub CheckData()
Dim i As Long
Dim blFailed As Boolean
Dim col As Long
col = 19  '34 to 40 are good
col2 = 3

ActiveSheet.Activate
ActiveSheet.Unprotect

For i = 12 To 50
    With ActiveSheet.Cells(i, "F")
        If .Value = "MAX" Then
           If .Offset(0, -1).Value = "" Then
                 blFailed = True
                 .Offset(0, -1).Interior.ColorIndex = col2
             Else
                 .Offset(0, -1).Interior.ColorIndex = col
             End If
             
             If .Offset(0, -2).Value = "" Then
                  blFailed = True
                 .Offset(0, -2).Interior.ColorIndex = col2
             Else
                 .Offset(0, -2).Interior.ColorIndex = col
             End If
             If .Offset(0, -4).Value = "" Then
                 blFailed = True
                 .Offset(0, -4).Interior.ColorIndex = col2
             Else
                 .Offset(0, -4).Interior.ColorIndex = col
             End If
             If .Offset(0, 3).Value = "" Then
                 blFailed = True
                 .Offset(0, 3).Interior.ColorIndex = col2
             Else
                 .Offset(0, 3).Interior.ColorIndex = col
             End If
             If .Offset(0, 19).Value = "" Then
                 blFailed = True
                 .Offset(0, 19).Interior.ColorIndex = col2
            Else
                 .Offset(0, 19).Interior.ColorIndex = col
             End If
        End If
    End With
Next i
Range("A12").Select

    If blFailed Then
        MsgBox "Cannot SAVE file! All Cells Colored Red need to be filled in", vbExclamation, "Save Cancelled"
        Cancel = True
    End If


ActiveSheet.Protect
End Sub
0
mato01
Asked:
mato01
  • 2
1 Solution
 
Patrick MatthewsCommented:
If I go in and hit the spacebar to empty it ignores the value = "".  

So for example hte

  If .Offset(0, -1).Value = "" Then

can appear empty, but the script does not treat it as such.

Well, you DO NOT "empty" a cell by entering a space.  By doing that, you enter a value: the space.

An empty cell is one that has neither a constant (value) nor a formula.  Thus, a formula that evaluates to a zero length string is NOT empty.

If you want to test whether a cell is empty, use:

  If IsEmpty(.Offset(0, -1)) Then

Open in new window


0
 
byundtCommented:
Or you could use Trim:

If Trim(.Offset(0, -1).Value) = "" Then
0
 
Patrick MatthewsCommented:
Brad,

Depends on what we're looking for.  Do we need an "empty" cell, in which case Trim won't help us (see above), or do we want "a cell that is empty, or all spaces, or a formula that evaluates to a zero length string or all spaces"?

:)

Patrick
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now