CODEWITHSUNDEEP
loopsexcelvba
franklin
franklin

Reputation: 1819

exit a nested loop in excel

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 from REGEXP (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.

Upvotes: 4

Views: 18887

Answers (1)

vansimke
vansimke

Reputation: 974

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...

Upvotes: 4

Related Questions