Variable Value Being Returned as 0 (zero) for empty cells

[Ark68]

I have this code:

       . . .      
       For i = 2 To lstrow
            mcval = .Range("J" & i)
            If IsNumeric(mcval) Then
                .Range("W" & i) = "YES"
            Else
                .Range("W" & i) = "NO"
            End If
        Next i
        . . .

It acquires the value of mcval from a cell in column J (Ji). It checks to see if it's numeric and simply reacts to whether it is or isn't. Normally, column J will either hold dates (numbers), or be empty.
When I run this code, mcval = 0 for empty cells, and thus is numeric. This isn't wrong of course, but I'm trying to identify the empty cells. I suspect it might have something to do with having declared mcval as long.
What must I change to ensure that the value for mcval on empty cells is null vs zero?

 

[Joe4]

No need to set it equal to a variable.
Try this to check to see if it is a number AND not empty:

       For i = 2 To lstrow
            If IsNumeric(.Range("J" & i)) And (.Range("J" & i) <>"") Then
                .Range("W" & i) = "YES"
            Else
                .Range("W" & i) = "NO"
            End If
        Next i

 

[RoryA]

I'd test for empty cells specifically.

 

[Artik]

I suspect it might have something to do with having declared mcval as long.

And yes and no.
If you take an empty cell to a variable of type Long, the variable actually takes the value 0. But the IsNumeric(Empty) test also returns 0 (Empty, is the value returned by an empty cell).

What must I change to ensure that the value for mcval on empty cells is null vs zero?

My suggestion:

    ...
    
    Dim v As Variant
    Dim vResult As Variant

    v = .Range("J2:" & lstrow).Value
    ReDim vResult(1 To UBound(v), 1 To 1)

    For i = 1 To UBound(v)
        If Len(v(i, 1)) <> 0 Then
            If IsNumeric(v(i, 1)) Then
                vResult(i, 1) = "YES"
            Else
                vResult(i, 1) = "NO"
            End If
        Else
            vResult(i, 1) = "NO"
        End If
    Next i

    .Range("W2").Resize(UBound(vResult)).Value = vResult

    ...

It is not worth creating a compound condition on one line because both (or more) conditions must be tested. When the compound condition is written as a separate one, the code has a chance to execute faster because not all conditions need to be tested.
On the other hand, it is worthwhile to take the data used in the loop from an array rather than a cell, since reading from an array is many times faster.

Artik

 

[Ark68]

Thank you all for your suggestions. Providing different methods to accomplish the same result is very educational and I find that invaluable. That alone is valued more than the solution itself.

 

[Joe4]

You are welcome.
Glad we were able to help!