Solved

"" not treated as empty

Posted on 2011-09-04
3
271 Views
Last Modified: 2012-06-27
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
Comment
Question by:mato01
  • 2
3 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36481023
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
 
LVL 80

Accepted Solution

by:
byundt earned 125 total points
ID: 36481030
Or you could use Trim:

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

Expert Comment

by:Patrick Matthews
ID: 36481052
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now