CODEWITHSUNDEEP
vbaexcel
Tzvibe
Tzvibe

Reputation: 369

Excel VBA - how to skip to next iteration in a Do Until...Loop

I'm trying to skip to the next iteration in my vba code, with the 'continue' statement. Its not working..

  Do Until IsEmpty(xlSheet.Cells(row, 1))
       row = row + 1
       If xlSheet.Cells(row, 2) = "Epic" Then
          Continue
       End If
       xlSheet.Cells(row, 1).Value = 5
  Loop

Any idea?? Thanks..

Upvotes: 8

Views: 49322

Answers (4)

Fernando Garcia
Fernando Garcia

Reputation: 1

You can make something like this due to vba have not any continue statement using the Goto statement use for errors.

Do Until IsEmpty(xlSheet.Cells(row, 1))
       row = row + 1
       If xlSheet.Cells(row, 2) = "Epic" Then
          GoTo Exitloop
       End If
       xlSheet.Cells(row, 1).Value = 5
ExitLoop:
  Loop

Upvotes: 0

Caston
Caston

Reputation: 177

While Visual Basic has 3 continue statements for 3 different loops (Continue While, Continue Do, and Continue For), oddly Microsoft VBA does not use any of them. You are going to have to restructure your loops and conditionals to go without.

Upvotes: 0

Rob Duikersloot
Rob Duikersloot

Reputation: 11

So this is another possibility that works for me. Just skip the unwanted lines...

Do Until IsEmpty(xlSheet.Cells(row, 1))
   row = row + 1
   Do while xlSheet.Cells(row, 2) = "Epic" 
      row = row + 1
   Loop
   xlSheet.Cells(row, 1).Value = 5
Loop

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

VBA does not have a Continue statement. You can get around it by doing something like

Do Until IsEmpty(xlSheet.Cells(row + 1, 1))
   row = row + 1
   If xlSheet.Cells(row, 2) <> "Epic" Then
       xlSheet.Cells(row, 1).Value = 5
   End If
Loop

or

Do Until IsEmpty(xlSheet.Cells(row + 1, 1))
    row = row + 1
    If xlSheet.Cells(row, 2) = "Epic" Then
        GoTo ContinueDo
    End If
    xlSheet.Cells(row, 1).Value = 5
ContinueDo:
Loop

Note: In both versions I changed IsEmpty(xlSheet.Cells(row, 1)) to IsEmpty(xlSheet.Cells(row + 1, 1)) or else you will end up with an infinite loop.

Upvotes: 8

Related Questions