WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Break For Loop

VBA Break For Loop

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Jeevan A Y

Excel VBA Break For Loop

In VBA Break For Loop is also known as exit for loop, every loop in any procedure has been given som11e set of instructions or criteria for it to run nuber of time but it is very common that some loop get into an infinite loop thus corrupting the code in such scenarios we need break for or exit for loop to come out of certain situations.

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 it completes the full loop quota of 10. In this article, we will show you how to exit the loop based on the criteria given.

VBA Break For Loop

How to Break/Exit Loops in VBA?

You can download this VBA Break For Loop Excel Template here – VBA Break For Loop Excel Template

#1 – Break For Next Loop

VBA For Next Loop is used to loop over cells and perform a specific set of tasks. For 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.

Break For Loop Example 1

This is the obvious thing with For Next Loop.

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

Break For Loop Example 1-1

Now I 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.”

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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.

Break For Loop Example 1-2

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

Exit For Next Loop 1-3

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

#2 – Break Do Until Loop

Like how we have exited For Next Loop, similarly, we can exit the “Do Until” 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.

Exit Do Until Loop

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/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 Keyword
  • VBA Chr
  • VBA UsedRange
  • VBA Wait
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel, VBA & Power BI,  Learn VBA

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Break For Loop Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More