VBA Break For Loop

Excel VBA Break For Loop

Is it possible to exit the for loop in excel VBA before its maturity? The answer to this is absolute YES! Let’s say we have instructed the loop to run for 10 times and based on the condition given if the cell value or any other supplied criteria is successful then it has to exit the excel loop before its complete the full lop quota of 10. In this article, we will show you how to exit the loop based on the criteria’s given. Follow the article to learn about VBA Break For Loop.

#1 – Exit For Next VBA Loop

VBA For Next Loop is used to loop over cells and perform a specific set of tasks. For an example look at the below VBA code.

Code:

Sub Exit_Loop()

 Dim K As Long

 For K = 1 To 10
  Cells(K, 1).Value = K
 Next K

End Sub

This will insert serial numbers from cell A1 to A10.

This is the obvious thing with For Next Loop.

Now I want to break the VBA loop when any value found in the first 10 cells, for this I have entered some text value in the cell A8.

Now I want to instruct this in the code saying “if the looping cell has certain value it has to exit the loop before the pre-determined limit”.

Code:

Sub Exit_Loop()

 Dim K As Long

 For K = 1 To 10
  If Cells(K, 1).Value = "" Then
   Cells(K, 1).Value = K
  Else
   Exit For
  End If
 Next K

End Sub

Look these line of code:

If Cells(K, 1).Value = “” Then
Cells(K, 1).Value = K
Else
  Exit For
End If

It says If Cells(K, 1).Value = “” looping cell is equal to nothing continue the loop of inserting serial numbers from 1 to 10. Cells(K, 1).Value = K

The last part of the loop says:

Else

  Exit For

If the above condition is not TRUE then “Exit For” loop.

Now run the code it will insert serial numbers until the A7 cell.

The above code straight away exited the loop without saying anything, how do we know it has exited the loop.

To clear this ambiguity we need to put one simple VBA message box as below.

Code:

Sub Exit_Loop()

 Dim K As Long

 For K = 1 To 10
  If Cells(K, 1).Value = "" Then
   Cells(K, 1).Value = K
  Else
   MsgBox "We got non empty cell, in cell " & 
   Cells(K, 1).Address & vbNewLine & "We are exiting the loop"
   Exit For
  End If
 Next K

End Sub

When looping through the cell if any non-empty cell founds it will display the message saying “We got non-empty cell, in cell A8. We are exiting the loop”.

This will inform the user of the exit of the loop with cell address as well. If any value entered by mistake then we can check the cell address returned in the message box.

#2 – Exit Do Until VBA Loop

Like how we have exited For Next Loop, similarly we can exit the “Do Until” VBA loop as well. For example, look at the below code.

Code:

Sub Exit_DoUntil_Loop()

 Dim K As Long
 K = 1

 Do Until K = 11
   Cells(K, 1).Value = K
   K = K + 1
 Loop

End Sub

This code also performs the task of inserting serial numbers. For example, if we wish to exit the loop when the variable “k” value becomes 6, we need to enter the criteria as IF  k = 6 then exit the loop.

Code:

Sub Exit_DoUntil_Loop()

 Dim K As Long
 K = 1

 Do Until K = 11
  If K < 6 Then
  Cells(K, 1).Value = K
  Else
   Exit Do
 End If
 K = K + 1
Loop

End Sub

This will run the loop until the variable value becomes 6, after that it will exit the loop. If you wish to show the message to the user then you can add the message box as well.

Code:

Sub Exit_DoUntil_Loop()

 Dim K As Long
 K = 1

 Do Until K = 11
  If K < 6 Then
  Cells(K, 1).Value = K
  Else
 MsgBox "We are exiting the loop because k value is >5"
  Exit Do
  End If
  K = K + 1
 Loop

End Sub

This will show the message below.

Like this based on the criteria given, we can exit the loop if the criteria are TRUE or else we can continue the loop.

Recommended Articles

This has been a guide to VBA Break For Loop. Here we learn how to Exit VBA for Loop along with step by step examples and download template. Below are some useful excel articles related to VBA –

• GetObject Function in VBA
• INSTRREV in Excel VBA
• User-Defined Function in Excel VBA
• VBA Chr
• Using For Each Loop in VBA
• Loop in Excel VBA
• UsedRange in Excel VBA
• Wait in Excel VBA