Continue For loop

Question

I have the following code:

For x = LBound(arr) To UBound(arr)
  
    sname = arr(x)  
    If InStr(sname, "Configuration item") Then  
        '**(here I want to go to next x in loop and not complete the code below)**  
    End If

    '// other code to copy past and do various stuff

Next x

I thought I could simply have the statement Then Next x, but this gives a "no for statement declared" error.

What can I put after the If InStr(sname, "Configuration item") Then to make it proceed to the next value for x?

Thank you to people correcting me spelling, I know I suck at it, and am grateful that people would take time to help me with it. Cheers — DevilWAH, Nov 20 '13 at 13:59

score 110 · Answer 1 · edited Mar 14 '17 at 09:26

110

You can use a GoTo:

Do

    '... do stuff your loop will be doing

    ' skip to the end of the loop if necessary:
    If <condition-to-go-to-next-iteration> Then GoTo ContinueLoop 

    '... do other stuff if the condition is not met

ContinueLoop:
Loop

edited Mar 14 '17 at 09:26

S.Serpooshan

7,608
4
33
61

answered Oct 18 '12 at 18:08

VBA hack

1,101
2
7
2

13

+1 on the logical and rational choice of the venerable goto statement http://www.drdobbs.com/cpp/what-dijkstra-said-was-harmful-about-got/228700940 – Richard Chambers Jan 19 '15 at 20:24

Jean-François Corbett · Accepted Answer · 2018-04-18T08:24:06.143

57

You're thinking of a continue statement like Java's or Python's, but VBA has no such native statement, and you can't use VBA's Next like that.

You could achieve something like what you're trying to do using a GoTo statement instead, but really, GoTo should be reserved for cases where the alternatives are contrived and impractical.

In your case with a single "continue" condition, there's a really simple, clean, and readable alternative:

    If Not InStr(sname, "Configuration item") Then
        '// other code to copy paste and do various stuff
    End If

edited Apr 18 '18 at 08:24

answered May 05 '11 at 10:25

Jean-François Corbett

37,420
30
139
188

12

This is less clean and readable when you have several conditions like throughout your loop. As the code gets more deeply nested it requires more headspace for the coder trying to read it. For that reason GoTo might be better here, and Arlen Beiler's answer is another decent solution. – pettys May 27 '16 at 15:32
1

I agree, those would be better answers — to a different question. Not to this one. – Jean-François Corbett May 28 '16 at 10:18
It sounds like we agree that, for those looking for a more general approach to VBA's lack of a "continue" statement, the alternative answers below have advantages. My intent was simply to deepen the discussion by weighing the trade-offs in the general case. – pettys May 31 '16 at 17:26
1

I work by a rule: "Check your preconditions, if they fail, then bail" Start of a method, or start of a loop, same deal. I'd use continue if I could, but instead I use a Goto because it stops levels of nesting. – Roger Willcocks Apr 17 '18 at 02:16
I'm not sure why everyone here is focusing on "multiple levels of nesting" when the question clearly has only one?... – Jean-François Corbett Dec 15 '18 at 15:34
This comment chain might be old, but to throw my hat in the ring. It's because people find this question when they are frustrated by the deep nesting that seemed almost unavoidable without continue, only to be rescued by goto. The goto is more practical and maintainable in all but the most trivial of cases. – Gry- Mar 05 '23 at 12:16

score 28 · Answer 3 · answered Jan 19 '15 at 20:39

28

For i=1 To 10
    Do 
        'Do everything in here and

        If I_Dont_Want_Finish_This_Loop Then
            Exit Do
        End If 

        'Of course, if I do want to finish it,
        'I put more stuff here, and then...

    Loop While False 'quit after one loop
Next i

answered Jan 19 '15 at 20:39

Arlen Beiler

15,336
34
92
135

This looks to be the neatest way to get out of using Goto for continuing For loops I've seen. I Imagine you could adapt the same approach for avoiding Goto in other circumstances too, for that matter... – tobriand May 29 '15 at 16:24
1

Good answer. Alfredo Yong's answer is the same idea, but Alfredo's answer's compactness makes it more readable for me. – pettys May 27 '16 at 15:33
1

Wow. Ladies and gentleman, the "Do Once or Exit Do to Continue" idiom. – Jean-François Corbett Apr 18 '18 at 08:26
This behaves lika a goto, but breaks the ability of reaching unpredicted states, that is inherent to gotos. - Still makes the code more complicated to read, especially if the stuff one wants to do is a lot. - In my opinion, this is useful IFF the logical inversion of the condition gets too complicated to read or understand easily. – I'm with Monica Jan 13 '20 at 07:50

score 21 · Answer 4 · answered Dec 22 '15 at 16:40

21

A lot of years after... I like this one:

For x = LBound(arr) To UBound(arr): Do

    sname = arr(x)  
    If instr(sname, "Configuration item") Then Exit Do 

    '// other code to copy past and do various stuff

Loop While False: Next x

answered Dec 22 '15 at 16:40

Alfredo Yong

976
8
11

8

Um, this is basically the same thing as [Arlen Beiler's earlier answer](https://stackoverflow.com/a/28033105/119775), with a couple fewer line breaks... – Jean-François Corbett Apr 18 '18 at 08:28

score 8 · Answer 5 · answered Apr 09 '14 at 07:10

8

A few years late, but here is another alternative.

For x = LBound(arr) To UBound(arr)
    sname = arr(x)  
    If InStr(sname, "Configuration item") Then  
        'Do nothing here, which automatically go to the next iteration
    Else
        'Code to perform the required action
    End If
Next x

answered Apr 09 '14 at 07:10

moongster

189
2
8

2

yeah, but this is the standard choice for the problem at hand causing ugly nesting if it is more than one `continue` condition – Andreas Covidiot Dec 17 '18 at 09:58

score 2 · Answer 6 · answered Dec 17 '21 at 02:31

And many years later :D I used a "select" statement for a simple example:

  For Each zThisRow In zRowRange
    zRowNum = zThisRow.Row
    Select Case zRowNum
      Case 1 '- Skip header row and any other rows to skip -----
             '- no need to put anything here -----

      Case Else '- Rows to process -----
             '- Process for stuff to do something here -----
    End Select
  Next zThisRow

You can make this as complex as you wish by turning each "if" result into a value (maybe a bit of over complex code would help explain :D ):

zSkip = 0
If 'condition1 = skip' Then zSkip = zSkip + 1
If 'condition2 = skip' Then zSkip = zSkip + 1
If 'condition3 = skip' Then zSkip = zSkip + 1
Select Case zRowNum
  Case 0 '- Stuff to do -----
  Case Else '- Stuff to skip -----
End Select

It's just a suggestion; have a great Christmas peeps!

score 0 · Answer 7 · answered Jun 20 '18 at 02:43

This can also be solved using a boolean.

For Each rngCol In rngAll.Columns
    doCol = False '<==== Resets to False at top of each column
    For Each cell In Selection
        If cell.row = 1 Then
            If thisColumnShouldBeProcessed Then doCol = True
        End If
        If doCol Then
            'Do what you want to do to each cell in this column
        End If
    Next cell
Next rngCol

For example, here is the full example that:
(1) Identifies range of used cells on worksheet
(2) Loops through each column
(3) IF column title is an accepted title, Loops through all cells in the column

Sub HowToSkipForLoopIfConditionNotMet()
    Dim rngCol, rngAll, cell As Range, cnt As Long, doCol, cellValType As Boolean
    Set rngAll = Range("A1").CurrentRegion
    'MsgBox R.Address(0, 0), , "All data"
    cnt = 0
    For Each rngCol In rngAll.Columns
        rngCol.Select
        doCol = False
        For Each cell In Selection
            If cell.row = 1 Then
                If cell.Value = "AnAllowedColumnTitle" Then doCol = True
            End If
            If doCol Then '<============== THIS LINE ==========
                cnt = cnt + 1
                Debug.Print ("[" & cell.Value & "]" & " / " & cell.Address & " / " & cell.Column & " / " & cell.row)
                If cnt > 5 Then End '<=== NOT NEEDED. Just prevents too much demo output.
            End If
        Next cell
    Next rngCol
End Sub

Note: If you didn't immediately catch it, the line If docol Then is your inverted CONTINUE. That is, if doCol remains False, the script CONTINUES to the next cell and doesn't do anything.

Certainly not as fast/efficient as a proper continue or next for statement, but the end result is as close as I've been able to get.

score 0 · Answer 8 · edited Oct 20 '21 at 17:13

0

you can do that by simple way, simply change the variable value that used in for loop to the end value as shown in example

Sub TEST_ONLY()
    For i = 1 To 10
        ActiveSheet.Cells(i, 1).Value = i
        If i = 5 Then
            i = 10
        End If
    Next i
End Sub

edited Oct 20 '21 at 17:13

BigBen

46,229
7
24
40

answered Feb 12 '21 at 20:57

Mazin Alwaaly

9
1

No (-1). Your proposal will end the loop altogether (no instruction executed for `i>=6`), while also executing the instructions which might be between the `End If` and the `Next i`; the question was about obtaining the reverse effect: to skip the instructions after `End If` while continuing the loop with the next value. Also instead of writing `i = 10`, it is clearer to write `Exit For`. – AntoineL Nov 29 '22 at 13:12

score -1 · Answer 9 · edited May 29 '15 at 18:08

-1

I sometimes do a double do loop:

Do

    Do

        If I_Don't_Want_to_Finish_This_Loop Then Exit Do

        Exit Do

    Loop

Loop Until Done

This avoids having "goto spaghetti"

edited May 29 '15 at 18:08

Arlen Beiler

15,336
34
92
135

answered May 10 '15 at 15:07

Rand

1

This is pretty much the same thing as [Arlen Beiler's earlier answer](https://stackoverflow.com/a/28033105/119775). – Jean-François Corbett Apr 18 '18 at 08:28

Continue For loop

9 Answers9

Linked

Related