Why won't this skip non-numeric values?

Question

I am trying to iterate over an array and skip the non-numeric values, I have put an if loop inside a for loop with the intention that it should identify non numeric values if so +1 to the iterator, however it is not working, producing no error message as to why

For i = 1 To 780

    If Not IsNumeric(Worksheets("sheet").Cells(6 + i, 6)) Then
       i = i + 1
    End If

Worksheets("one").Cells(6 + i, 6) = Worksheets("one").Cells(6 + i, 6) * Worksheets("two").Cells(3 + i, 6)      

Next

I am new to VBA and do not understand why this wouldn't work

Modifying the loop variable inside the loop body is asking for trouble and off-by-one bugs. Exactly what are you trying to do? — Mathieu Guindon
– Mathieu Guindon, Commented Oct 25, 2016 at 16:47

Comintern · Accepted Answer · 2016-10-25 16:48:51Z

2

Incrementing your loop counter inside the loop skips the next item, not the current one. Assuming that .... is the code you want to execute if the value is numeric, reverse your condition and only run that code for numeric values:

For i = 1 To 780
    If IsNumeric(Worksheets("sheet").Cells(6 + i, 6)) Then
       '....    
    End If
Next

Note that your code also assumes that empty cells have a value of 0. If .Cells(6 + i, 6) is empty, it will return Empty, which will implicitly cast to 0:

Range("A1").ClearContents
Debug.Print IsNumeric(Range("A1")) '<-- True

If you need to ignore empty cells also, you'll need an additional test:

If Not IsEmpty(Worksheets("sheet").Cells(6 + i, 6)) Then
    If IsNumeric(Worksheets("sheet").Cells(6 + i, 6)) Then
       '....
    End If
End If

answered Oct 25, 2016 at 16:48

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Craner Over a year ago

You code review guys come into my house and wax poetic, and thus give a better answer. Why I aughta' ...... sheeeeeesh :)

Comintern Over a year ago

@ScottCraner - LOL. Just trying to pre-emptively answer the OP's next n questions.

Scott Craner · Accepted Answer · 2016-10-25 16:47:00Z

2

Instead of looking for the negative look for the positive:

For i = 1 To 780

    If IsNumeric(Worksheets("sheet").Cells(6 + i, 6)) Then
       'Do your code when it is numeric
       'It will skip to the next loop if not numeric
    End If
Next

answered Oct 25, 2016 at 16:47

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Comments

user3598756 · Accepted Answer · 2016-10-25 17:42:02Z

2

use SpecialCells() method of Range object to loop through your range "numeric" cells only:

    Dim cell As Range        
    With Worksheets("sheet")
        For Each cell In .Range(.Cells(7, 6), .Cells(786, 6)).SpecialCells(xlCellTypeConstants, xlNumbers)

            ' your code for numeric values

        Next cell
    End With

answered Oct 25, 2016 at 17:42

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

Why won't this skip non-numeric values?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related