Skip to next iteration in loop vba

Question

I am trying to create a simple conditional loop that will go to the next iteration if a condition is true. The code I have so far is:

For i = 2 To 24
    Level = Cells(i, 4)
    Return = Cells(i, 5)

    If Return = 0 And Level = 0 Then
        'Go to the next iteration
    Else
    End If
Next

I have tried GoTo NextIteration, but this comes up with the error 'Label not defined'. This probably has a very simple solution, but assistance would be much appreciated. Thanks.

whats Level whats Return, what does your spreadsheet look like — , Dec 19 '13 at 12:07
Invert the clause and drop throught? - or http://stackoverflow.com/questions/5895908/continue-for-loop — Alex K., Dec 19 '13 at 12:09
There is no `Continue` in VbScript. You would have to play around `If-Else` to skip any iteration. Go thru the link posted by @AlexK. It has some good suggestions. — Pankaj Jaju, Dec 19 '13 at 12:11
Both Integers, I am trying to make sure that the code doesnt run if both these values are 0. — Käse, Dec 19 '13 at 12:32
Nobody ever said anything about `Return` shouldn't be used... (you know... it is used for other stuff and may lead to huge problems if used as a variable) — Dirk Reichel, Mar 12 '19 at 13:35

B H · Answer 1 · 2021-11-10T20:23:51.370

60

For i = 2 To 24
  Level = Cells(i, 4)
  Return = Cells(i, 5)

  If Return = 0 And Level = 0 Then
    'Go to the next iteration
    GoTo NextIteration
  Else
  End If
  ' This is how you make a line label in VBA - Do not use keyword or
  ' integer and end it in colon
  NextIteration:
Next

edited Nov 10 '21 at 20:23

answered May 26 '15 at 03:30

B H

1,730
18
24

Why the `else` after the `if`? I get `end if without if block`. – Timo Nov 06 '21 at 10:55
2

It was in the OP's posted code. – B H Nov 08 '21 at 20:00
I see, IT life is not copy and paste. – Timo Nov 09 '21 at 19:21
@timo: It might not have worked like I originally had it so I moved the actual executed 'GoTo' under the comment the way that most people would be accustomed to looking at it. – B H Nov 10 '21 at 20:25

Tanner · Answer 2 · 2014-12-17T08:46:42.280

17

Just do nothing once the criteria is met, otherwise do the processing you require and the For loop will go to the next item.

For i = 2 To 24
    Level = Cells(i, 4)
    Return = Cells(i, 5)

    If Return = 0 And Level = 0 Then
        'Do nothing
    Else
        'Do something
    End If
Next i

Or change the clause so it only processes if the conditions are met:

For i = 2 To 24
    Level = Cells(i, 4)
    Return = Cells(i, 5)

    If Return <> 0 Or Level <> 0 Then
        'Do something
    End If
Next i

edited Dec 17 '14 at 08:46

answered Dec 19 '13 at 12:04

Tanner

22,205
9
65
83

shouldn't it be If Return <> 0 Or Level <> 0 Then, so it's the opposite to If Return = 0 And Level = 0 Then? – brWHigino Dec 19 '13 at 12:15
The second one seems to work, but I think I need an Or instead of an And. There needs to be an Else too I think. – Käse Dec 19 '13 at 12:47

score 7 · Answer 3 · answered Aug 30 '17 at 19:02

7

I use Goto

  For x= 1 to 20

       If something then goto continue

       skip this code

  Continue:

  Next x

answered Aug 30 '17 at 19:02

Sam

87
1
1

1

The simplest (= best) solution (I upvoted), but "skip this code" should read "skip this code if 'something' is true". – dcromley May 06 '19 at 17:17

sancho.s ReinstateMonicaCellio · Answer 4 · 2018-07-05T06:20:13.887

The present solution produces the same flow as your OP. It does not use Labels, but this was not a requirement of the OP. You only asked for "a simple conditional loop that will go to the next iteration if a condition is true", and since this is cleaner to read, it is likely a better option than that using a Label.

What you want inside your for loop follows the pattern

If (your condition) Then
    'Do something
End If

In this case, your condition is Not(Return = 0 And Level = 0), so you would use

For i = 2 To 24
    Level = Cells(i, 4)
    Return = Cells(i, 5)

    If (Not(Return = 0 And Level = 0)) Then
        'Do something
    End If
Next i

PS: the condition is equivalent to (Return <> 0 Or Level <> 0)

score 2 · Answer 5 · answered May 24 '21 at 08:34

2

You can use a kind of continue by using a nested Do ... Loop While False:

'This sample will output 1 and 3 only

Dim i As Integer

For i = 1 To 3: Do

    If i = 2 Then Exit Do 'Exit Do is the Continue

    Debug.Print i

Loop While False: Next i

answered May 24 '21 at 08:34

AHeyne

3,377
2
11
16

Interesting idea, but not scalable as you then can't have another "do" within the for loop that you can exit from. I guess that's true of all the loop exit options, you can only exit from one of them. You can't loop out from a for-within-a-for to the start of the outer one. – PhilHibbs Apr 19 '22 at 11:38

Skip to next iteration in loop vba

5 Answers5

Linked