If-Else Simple Loop function

[Junior728]

hi,

i am trying to use a If-Else Loop or If-ElseIf-Else loop,but the VB editor
keep prompting me a mistake with Use "Next..without For" comment. Is it
something wrong with my if-else function or otherwise. Pls advise.

My Example:

Sub COMPAREWH()

Dim LastRow

Range("A65536").Select
Selection.End(xlUp).Select

LastRow = ActiveCell.Row
LR$ = LastRow

Range("A2").Select
Sheets("Tabelle1").Select

'Start of For...Next Loop
For i = ActiveCell.Row To LastRow

COMPARE$ = Cells(i, 1).Value
Vlook$ = Cells(i, 4).Value

If COMPARE$ = "1" Then GoTo Skiptohere

ElseIf COMPARE$ = "2" Then
Cells(i, ActiveCell.Column).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Else: GotoSkiptohere
End If

Next i

Skiptohere:
ActiveCell.Offset(1, 0).Range("A1").Select


End Sub

 

[smcferrin]

It appears to me that you are prematurely exiting the For. . .Next loop
when you go to Skiptohere.

Steve

 

[Norman Jones]

Hi Junior728,

Try this adaptation of your code:

Sub COMPAREWH()
Dim LastRow As Long
Dim sh As Worksheet
Dim vCompare As Variant
Dim vVlook As Variant
Dim i As Long

Set sh = ThisWorkbook.Sheets("Tabelle")

LastRow = sh.Cells(Rows.Count, "A").End(xlUp).Row

'Start of For...Next Loop
For i = 2 To LastRow
vCompare = Cells(i, 1).Value
vVlook = Cells(i, 4).Value

If vCompare = 1 Then
Cells(i + 1, "A").Select
Exit For
ElseIf vCompare = 2 Then
With Cells(i, "A").Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next i

End Sub

Note that I have removed all selections (except for one). It is rarely
necessary, and usualyy less efficient, to make physical selections.

Test the revised code on a *copy* of your workbook to ensure that my
intepretation properly reflects your requirements.

 

[Bob Phillips]

Haven't tested it, but this line

If COMPARE$ = "1" Then GoTo Skiptohere

is self contianed, there can be no Else associated with it beacuse you have
included the action on the same line. Your code whould be like

If COMPARE$ = "1" Then
GoTo Skiptohere
ElseIf COMPARE$ = "2" Then

or better still

If COMPARE$ = "1" Then
Exit For
ElseIf COMPARE$ = "2" Then