exit a nested loop in excel
I see two options.
1) Use a series of booleans to check if the outer loops should continue
2) Use a goto statement to exit from the inner loop directly back to the main procedure
In this situation, the goto might not be so bad since you aren't jumping to a completely different part of the code. Just document it well...
franklin
Former web application developer turned technology attorney. Find me at many of the common internet watering holes! Twtr | Mdm | Rdg | GitHub Toodaloo!
Updated on June 05, 2022Comments
-
franklin almost 2 years
i have a loop in a macro i'm writing of the following structure:
there are two worksheets in this book. raw data (hence the endpointData variable) and a G/L (General ledger) sheet (hence the endpointGL variable)
there are three
for
loops in this function:(1) the first loop iterates through each record in the raw data file.
(2) the second loop iterates through the verified matches fromREGEXP
(a regular expression search) and
(3) the third loop goes through the G/L and finds a corresponding match and puts (using:PUT_DATA_RANGE
) that data into the appropriate spot.Here's sort of what i'm getting at:
psuedocode:
For i = 2 To endpointData If SOME_TEST = True Then Set MATCHES = REGEXP.EXECUTE() For Each myMatch In MATCHES For j = 1 To endpointGL If myMatch.value = SOME_CONDITION Then PUT_DATA_RANGE Exit For ElseIf myMatch.value <> SOME_CONDITION Then MsgBox ("there might be a problem") ' EXIT BOTH LOOPS HERE ' write handler code Next End If Next End If Next i
now you'll notice that i have a few comments to myself. if the third loops finds no match in the G/L the code currently interrupts to notify the user. but that message box
MsgBox("there might be a problem")
is looped through along with the third loop. how do i get excel to exit BOTH loops and bring the FIRST for loop to the next availabe record in the raw data?by the way, i've tried exiting it with an
Exit For
but that doesn't seem to work exactly.