CODEWITHSUNDEEP
vbaexcel
Rick FlyFish
Rick FlyFish

Reputation: 105

If IsNumeric(num) And Len(num) > 1 evaluates false for a number?

DUH, I figured it out I had Len(num) > 1 and the numbers in column A start at 0 to 9 and repeat.

     For r = 58 To 616

    num = Range("A" & r).Value
    If IsNumeric(num) And Len(num) > 0 Then

        Range("H" & r).Value = Range("I56").Value

        Range("I" & r).Select
        Application.CutCopyMode = False
        ActiveCell.Formula = "=ABS(I54-H" & r & ")"
        Range("J" & r).Select
        ActiveCell.Formula = "=D" & r & "+(I55*I" & r & ")"
        Range("J" & r).Select                                   'Set value
    End If


Next r

Upvotes: 0

Views: 475

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3259

You probably just want to adjust it so that excel checks the range's Value for whether it's numeric instead of num. Also, you likely only want to check if the length is greater than 0, because that means something exists in the cell.

If IsNumeric(Range("A" & r).Value) And Len(Range("A" & r).Value) > 0 Then

    Range("H" & r).Value = Range("I56").Value

    Range("I" & r).Formula = "=ABS(I54-H" & r & ")"
    Range("J" & r).Formula = "=D" & r & "+(I55*I" & r & ")"

End If

Or if your variable num is already stored as an Integer or Long like below, you don't really need to do the isNumeric() check and, as @BigBen pointed out, Long and Integer variables will always evaluate to Len() > 0, so what you really want is to explicitly check that the number is greater than 0.

Dim num As Long
num = Range("A" & r).Value

If num > 0 Then

    Range("H" & r).Value = Range("I56").Value

    Range("I" & r).Formula = "=ABS(I54-H" & r & ")"
    Range("J" & r).Formula = "=D" & r & "+(I55*I" & r & ")"

End If

They both (should) do the same thing. You'll also notice that I got rid of the .Select calls in your code because it's good practice to remove it

Upvotes: 1

Related Questions