returning back to loop after using GoTo

Question

I have tried several methods but didn´t manage to succeed. My problem is - running loop1, if some condition is fulfilled, run loop2 until some condition is fulfilled, return back to loop1.

sub program()
Dim i As Integer
Dim q As Integer

For i=1 to 350
If Range("A"&i).value=1 And Range("D"&i).Value<15 Then Goto 1
Next i

1:q=0
  Do While List1.Range("A"&i+q).Value<>""
  Range("E"&i+q)="K"
  q=q+1
  Loop

End Sub

I haven't found the way how to return after executing "1 loop" back to "For Next Loop" and continue for next i. Maybe it is not possible and I have to include somehow code inside the first loop ?! thank you

I don't know VBA so can't make a full answer, but the thing you're trying to implement is a "state machine". A pretty powerful technique. — James Youngman, Mar 04 '12 at 14:13

score 3 · Answer 1 · answered Mar 04 '12 at 14:13

3

Make the code at 1 into a function and call that instead of using goto. When the function exits, your first loop will continue executing from where it left off.

answered Mar 04 '12 at 14:13

1

I have tried that but for some reason it was not working properly so have used the 2nd answer. But if somebody in future uses this method, it is necessary to declare variables as global - http://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba – jiri jansa Mar 04 '12 at 16:23

score 1 · Accepted Answer · answered Mar 04 '12 at 14:21

1

I don't actually know VBA (I last used VB in about 1996) but this should be more or less right:

sub program()
Dim i As Integer
Dim q As Integer

i = 1
Do while i <= 350
  If Range("A"&i).value=1 And Range("D"&i).Value<15 Then
    Do While i <= 350 And List1.Range("A"&i).Value<>""
      Range("E"&i)="K"
      i=i+1
    Loop
  Else
      i=i+1
  End If
Loop
end do
End Sub

answered Mar 04 '12 at 14:21

James Youngman

3,623
2
19
21

1

It works excellent. The code in VBA is without the statement "end do" (Do While is finished just by Loop). Thank you ! – jiri jansa Mar 04 '12 at 16:28

score 0 · Answer 3 · answered Mar 06 '17 at 03:05

Usage of GoTo is discouraged even by MSDN (you should use it only for error treatment paired with Resume), but since you asked, this is the approach:

Sub program()
    Dim i As Integer
    Dim q As Integer

    For i=1 to 350
        If ((Range("A"&i).value=1) And (Range("D"&i).Value<15)) Then Goto OtherLoop
FirstLoop:
    Next i

Exit Sub

OtherLoop:
   q=0
   Do While List1.Range("A"&i+q).Value<>""
       Range("E"&i+q)="K"
       q=q+1
   Loop
   Goto FirstLoop

End Sub

You need the Exit statement to keep your code from entering "OtherLoop" when it finishes "FirstLoop", and also need to tell it to go back to where you previously were. Again, avoid using this stuff. Loops within loops (indent to organize, please) and secondary procedure calls (calling another sub or function) are far more recommended.

returning back to loop after using GoTo

3 Answers3