Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If-Else Simple Loop function
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 Skiptohe ActiveCell.Offset(1, 0).Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If-Else Simple Loop function
It appears to me that you are prematurely exiting the For. . .Next loop when you go to Skiptohere. Steve -- smcferrin ------------------------------------------------------------------------ smcferrin's Profile: http://www.excelforum.com/member.php...o&userid=25761 View this thread: http://www.excelforum.com/showthread...hreadid=391715 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If-Else Simple Loop function
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. --- Regards, Norman "Junior728" wrote in message ... 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 Skiptohe ActiveCell.Offset(1, 0).Range("A1").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If-Else Simple Loop function
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 -- HTH Bob Phillips "Junior728" wrote in message ... 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 Skiptohe ActiveCell.Offset(1, 0).Range("A1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple loop needed please | Excel Discussion (Misc queries) | |||
I need a simple loop with a 4+ row added in. | Excel Discussion (Misc queries) | |||
Simple question on For...Next loop | Excel Programming | |||
Some help w/ simple loop, please? | Excel Programming | |||
simple loop macro | Excel Programming |