Excel VBA Break For Loop

Let us say we have instructed the loop to run 10 times. Based on the condition, if the cell value or any other supplied criteria is successful, it has to exit the Excel loop before completing the full loop quota of 10. This article will show you how to exit the loop based on the criteria.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Break For Loop (wallstreetmojo.com)

How to Break/Exit Loops in VBA?

#1 – Break For Next Loop

VBA For Next LoopVBA For Next LoopAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. read more is used to loop over cells and perform specific tasks. For example, look at the VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more below.

Code:

Sub Exit_Loop()

Dim K As Long

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

End Sub

It will insert serial numbers from cell A1 to A10.

It is the obvious thing with For Next Loop.

We want to break the loop when we find any value in the first ten cells. For this, we have entered some text values in cell A8.

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

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 at these lines of code:

If Cells(K, 1).Value = “” ThenCells(K, 1).Value = KElse  Exit ForEnd 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.

The last part of the loop says:

Else

  Exit For

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

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

The above code immediately 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 boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more below.

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 it finds any non-empty cell, it will display the message saying, “We got non empty cell, in cell A8. We are exiting the loop”.

It will also inform the user of the loop’s exit with a cell address. We can check the cell address returned in the message box if we enter any value by mistake.

#2 – Break Do Until Loop

Like how we have exited for Next Loop, we can also exit the “Do Until” loop. For example, look at the below 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 and then exit the loop.

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

It 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, you can add the message box.

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. Else, we can continue the loop.

This article has been a guide to VBA Break For Loop. Here, we learn how to exit/break the VBA loop along with step-by-step examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: –

  • VBA ME KeywordVBA ChrVBA UsedRangeVBA Wait