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

Question

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

what is `num`? how is it set? if it's dimmed as an int or long, there's no reason to use `isNumeric` — Marcucciboy2, Sep 10 '18 at 19:12
Then wouldn't it put a formula in a row if there was text there, if you just just Len — Rick FlyFish, Sep 10 '18 at 19:15
it's in a for loop, but I can't seem to figure out how to edit my question...lol and add the whole loop in — Rick FlyFish, Sep 10 '18 at 19:15
I did and I figured it out I had Len >1 so it would only work on numbers greater than 9 — Rick FlyFish, Sep 10 '18 at 19:22

Marcucciboy2 · Accepted Answer · 2018-09-10T19:41:55.353

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

Note that `Len` of an `Integer` is always 2, and of a `Long` is always 4. — BigBen, Sep 10 '18 at 19:21
@BigBen may as well post that as an answer since it's the *actual* reason why OP's snippet wasn't working. I'll vote that baby up. — Marcucciboy2, Sep 10 '18 at 19:28
Well if `num` was a `Long` or `Int`, then `Len(num) > 0` (or 1, not sure based on the latest edit) would always be true. It's a useful point but not sure if it was the root problem. — BigBen, Sep 10 '18 at 19:34

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

1 Answers1