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)**
'// other code to copy past and do various stuff
Next x
So I thought I could simply have the statement Then Next x
, but this gives a "no for statement declared" error.
So what can I put after the If instr(sname, "Configuration item") Then
to make it proceed to the next value for x?
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
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"
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
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
_x000D_
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
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.
For the case you do not use "DO": this is my solution for a FOR EACH with nested If conditional statements:
For Each line In lines
If <1st condition> Then
<code if 1st condition>
If <2nd condition> Then
If <3rd condition> Then
GoTo ContinueForEach
Else
<code else 3rd condition>
End If
Else
<code else 2nd condition>
End If
Else
<code else 1st condition>
End If
ContinueForEach:
Next
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
Source: Stackoverflow.com