Zero values being read-in as missing values in VBA

0
votes

I am trying to trace through a sheet and highlight cells that come back as blank values or 0 values (or "NaN") values. I wrote a nested for loop to do this for me, however, I am seeing a weird item.

The cell in question should change to red if the value equals 0. When I run my macro, however, the end result is that all the cells that pass the if statement conditions end as yellow, even if 0. Perhaps VBA reads blank cells as 0, or the cells are not truly blank? Have I made a mistake, or am I incorrect on the logic of VBA? 

    'i is for the columns
For i = 2 To 4 'lastColumn

    totalCounter = 0
    outageCounter = 0
    missingCounter = 0

    'j is for the rows
    For j = 6 To lastRow

        'highlight if production outage
        If mainSheet.Cells(j, i).Value = 0 Then
            mainSheet.Cells(j, i).Interior.Color = vbRed
            outageCounter = outageCounter + 1
        End If

        'highlight if comm outage
        If mainSheet.Cells(j, i).Value = "" Or mainSheet.Cells(j, i).Value = "NaN" Then
            mainSheet.Cells(j, i).Interior.Color = vbYellow
            missingCounter = missingCounter + 1
        End If

        totalCounter = totalCounter + 1

    Next j

    mainSheet.Cells(lastRow + 2, i).Value = missingCounter
    mainSheet.Cells(lastRow + 3, i).Value = outageCounter
    mainSheet.Cells(lastRow + 4, i).Value = totalCounter
2
vbaexcel
I believe you are correct--your initial IF will be true even when the value in the cell is blank. - vknowles
Curious: What is "NaN" as you're using it? AFAIK, Excel doesn't have a NaN value (as in "not a number"), unless your data just have the string "NaN" in there. - vknowles
Your code will set empty cells, cells containing "", and cells containing "NaN" to yellow (after first changing empty cells to red for a very short time). But there is no reason why your code should be changing cells containing 0 to be yellow as you say in your question ("all the cells that pass the if statement conditions end as yellow, even if 0"). Are you 100% certain that cells containing a 0 end up as yellow?!? - YowE3K
@YowE3K I think I can answer for the OP. When the cell has nothing in it, .Value will match either 0 or "" in the If statements, so, yes, both conditions will be satisfied. - vknowles
@vknowles - but that doesn't explain how a 0 ends up yellow - just why an empty cell ends up yellow (after being red for a short time). - YowE3K

2 Answers

3
votes

In Excel the default value of an empty cell is zero, so you need to check there's actually some content before checking for zero:

 Dim c As Range

 For j = 6 To lastRow

    Set c = mainSheet.Cells(j, i)

    'highlight if production outage
    If Len(c.Value) > 0 And c.Value = 0 Then
        c.Interior.Color = vbRed
        outageCounter = outageCounter + 1
    End If

    'highlight if comm outage
    If c.Value = "" Or c.Value = "NaN" Then
        c.Interior.Color = vbYellow
        missingCounter = missingCounter + 1
    End If

    totalCounter = totalCounter + 1

Next j
1
votes

you can use Text property and check it against 0 ("production outage") and "" or "NaN" ("comm outage")

you can also use some With - End With to reduce typing and memory accesses

With mainSheet
    For i = 2 To 4 'lastColumn

        totalCounter = 0
        outageCounter = 0
        missingCounter = 0

        'j is for the rows
        For j = 6 To lastrow

            With .Cells(j, i)
                Select Case .Text
                    Case 0
                        'highlight if production outage
                        .Interior.Color = vbRed
                        outageCounter = outageCounter + 1

                    Case "", "NaN"
                        'highlight if comm outage
                        .Interior.Color = vbYellow
                        missingCounter = missingCounter + 1
                End Select

                totalCounter = totalCounter + 1
            End With
        Next j

        .Cells(lastrow + 2, i).value = missingCounter
        .Cells(lastrow + 3, i).value = outageCounter
        .Cells(lastrow + 4, i).value = totalCounter
    Next i
End With