asked on

"" 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

Patrick Matthews

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

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

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

&quot;&quot; not treated as empty

"" not treated as empty