Using Next or GoTo or Case Select?

**Jiptastic** · 12-05-2012, 06:05 PM

I am checking to see if the value in column B is greater than the value of Column C. If the cell is empty, then I want the macro to skip to the next cell in the loop. When I write "next i" it errors "next without for". I know there are options to use GoTo or Case Select, but I couldn't get the syntax right on that either. Any suggestions?

Sub SKIP_ifcell_empty()

Dim i As Integer

For i = 2 To 1000

If Cells(i, 3).Value = Empty Then
next i 
End If

If Cells(i, 2).Value > Cells(i, 3).Value Then
Cells(i, 2).Interior.Color = 3000
End If



Next i

End Sub

**MarvinP** · 12-05-2012, 06:14 PM

Hi,

Is this what you want?

Sub SKIP_ifcell_empty()
    Dim i As Integer
        For i = 2 To 1000
            If Cells(i, "C").Value = Empty Then
                GoTo EmptyCell
            End If
            If Cells(i, "B").Value > Cells(i, "C").Value Then
                Cells(i, "B").Interior.Color = 3000
            End If
EmptyCell:
        Next i
End Sub

I think your error was you need a "For" for each "Next". You had two "Next" and only one "For". It is just the same as needing the same number of "(" as ")" in a formula or function.

**Jiptastic** · 12-05-2012, 07:41 PM

Okay cool. I didn't see a need to add another For. It looks like your GoTo solution is what I need. I was missing the ":" when I was trying that possibility before. I'll apply your solution tomorrow thanks.

**Richard Buttrey** · 12-05-2012, 08:13 PM

...or

Sub SKIP_ifcell_empty()

    Dim i As Integer

    For i = 2 To 1000
        If Cells(i, 3) <> Empty And Cells(i, 2).Value > Cells(i, 3).Value Then
            Cells(i, 2).Interior.Color = 3000
        End If
    Next i

End Sub

which avoids the need for any GoTo and the creation of 'spaghetti' code.