How to exit more than 1 for loop in Excel VBA?

17,803

Solution 1

If you nest a flag in your loops you can put an if statement prior to looping on your second loop. If the flag is true, then you exit the second loop as well.

'first for loop
for I = 1 to 5 
    do sth
    'second for loop
    for j = 2 to 7
        do sth
        'third for loop
        for m = 2 to 43
            if [condition] then 
               flg = True
               Exit for
            end if
        next
    If flg = True then Exit For
    next
next

Solution 2

Make dummy Do...Loop inclusion:

' first for loop
For i = 1 To 5
    ' do sth
    ' dummy do loop, won't repeat, just creating a block to exit from
    Do
        ' second for loop
        For j = 2 To 7
            ' do sth
            ' third for loop
            For m = 2 To 43
                If [Condition] Then
                   ' exit 2nd and 3rd loop and continue on next i
                   Exit Do
                End If
            Next
        Next
    Loop Until True ' never repeats
    ' continue within 1st for loop
Next

Solution 3

    For a = 1 To maxRows
        If Exam1Grade(a) > 99 Then
        For b = 1 To maxRows
            If Exam2Grade(b) > 99 Then
            For c = 1 To maxRows
                If Exam3Grade(c) >= 100 Then
                    MsgBox ("Stop looking through the 3rd exam, you have just found the perfect score")
                    GoTo ThisPoint 'Exit All For Loops
                ElseIf c = maxRows Then 'Restart At A
                    GoTo NextA
                End If
            Next c
            End If
        Next b
        End If
NextA:
    Next a
ThisPoint:

The GoTo statement can act as a break

Share:
17,803

Related videos on Youtube

Meng
Author by

Meng

2018 - Present Finished my master in Statistics. Quit machine learning. Now I am a developer. :) 2015-2017 Undergraduate Statistic student interested in machine learning and data mining. I always think of how to program easier, cleaner and faster, so I always ask questions like 'is there any simpler way to do it instead of blablabla'. People here are nice and intelligent! I will keep asking and asking and hopefully one day I am able to help others as well :D Cheers!

Updated on September 16, 2022

Comments

  • Meng
    Meng about 1 year

    The code is like:

    'first for loop
    for I = 1 to 5 
        do sth
        'second for loop
        for j = 2 to 7
            do sth
            'third for loop
            for m = 2 to 43
                if [condition] then 
                   exit 2nd and 3rd loop and continue on next I ?????
                end if
            next
        next
    next
    

    I wrote two Exit For, but it did not help. It only exited the 3rd for loop and continue on next j.

    • Kyle
      Kyle over 8 years
      Can you please post more complete code? I need to see where and how you entered Exit For to know what may be the issue.
  • Meng
    Meng over 8 years
    Thanks! That's what I am looking for!