In this article, we will demonstrate how to use the Exit Do command inside the Do While loop. The Exit Do command inside the Do While enables us to escape the code structure when necessary. In the following example, we extract the Order Id that belongs to a specific Customer Name and Order Status, then exit the loop using the Exit Do command.
Download Practice Workbook
VBA Excel to Exit Do While Loop: 4 Suitable Examples
Here are 4 examples of how to Exit the Do While in Excel VBA in various circumstances.
The examples require using the VBA code editor window. If you need assistance with opening it, please refer to this helper article.
Example 1 – Exit the Do While Loop When Specific Marks of a Student are Found
In this example, we will find the student’s marks based on the student’s id and subject using a do-while conditional loop. Using the Exit Do command, when we find our desired value, we’ll exit the loop.
- In the code editor, enter the following code, then click on the Run command:
Sub GetStudentDetails()
Dim studentMarks() As Variant
studentMarks = Range("B4:E19").Value
Dim studentID As String
studentID = InputBox("Enter the Student ID:")
Dim subject As String
subject = InputBox("Enter the Subject:")
Dim i As Integer
i = 1
Dim found As Boolean
found = False
Do While i <= UBound(studentMarks, 1)
If studentMarks(i, 1) = studentID And studentMarks(i, 2) = subject Then
MsgBox "Student ID: " & studentMarks(i, 1) & vbCrLf & _
"Subject: " & studentMarks(i, 2) & vbCrLf & _
"Mark: " & studentMarks(i, 3)
found = True
Exit Do
End If
i = i + 1
Loop
If Not found Then
MsgBox "Student ID and subject not found in the dataset."
End If
End Sub- After running the code, an input box pops up asking for the student’s id and then the subject.
VBA Code Breakdown
Sub GetStudentDetails()
Dim studentMarks() As Variant
studentMarks = Range("B4:E19").Value- Declares studentMarks as an array variable to store the values of a range.
- Range(“B4:E19”).Value retrieves the values from the range B4:E19 and assigns them to the studentMarks array.
Dim studentID As String
studentID = InputBox("Enter the Student ID:")
Dim subject As String
subject = InputBox("Enter the Subject:")- These lines declare studentID and subject as string variables.
- The InputBox function prompts the user to enter the Student ID and Subject, and their inputs are stored in the respective variables.
Dim i As Integer
i = 1
Dim found As Boolean
found = False- These lines declare i as an integer variable and found as a boolean variable.
- i is initialized to 1 and found is initialized to False.
Do While i <= UBound(studentMarks, 1)
If studentMarks(i, 1) = studentID And studentMarks(i, 2) = subject Then
MsgBox "Student ID: " & studentMarks(i, 1) & vbCrLf & _
"Subject: " & studentMarks(i, 2) & vbCrLf & _
"Mark: " & studentMarks(i, 3)
found = True
Exit Do
End If
i = i + 1
Loop- This block starts a “Do While” loop that continues as long as i is less than or equal to the upper bound of the first dimension of studentMarks (the number of rows).
- Inside the loop, it checks if the student ID and subject in the ith row of studentMarks match the values entered by the user. If there is a match, it displays a message box with the student ID, subject, and mark from the ith row of studentMarks. The found variable is then set to True to indicate that a match was found. Then the Exit Do statement is used to exit the loop prematurely.
- If no match is found, i is incremented by 1 in each iteration.
If Not found Then
MsgBox "Student ID and subject not found in the dataset."
End If
End Sub- This block checks if found is False (indicating that no match was found).
- If found is False at the end of the Loop, a message box is displayed indicating that the student ID and subject were not found in the dataset.
- The input box asking for the Student ID is shown below.
- After clicking OK, another input box asking for the subject is presented. Enter your desired subject name here.
- After entering the Student id and Subject name, the student’s marks are displayed in a message box.
Example 2 – Exit a Do While Loop When a Fixed Sales Target is
Met
In this code, we will determine for which customer we achieved our sales target.
- Open the VBA code editor and paste the following code in it, then Run the code.
Sub Exit_when_fixed_target_met()
Dim salesRange As Range
Set salesRange = Range("C5:C14")
Target_sales = ActiveSheet.Cells(5, 7).Value
Debug.Print Target_sales
Sum = 0
i = 5
jump:
Do While ActiveSheet.Cells(i, 3).Value = "Electronics"
Sum = Sum + ActiveSheet.Cells(i, 5).Value
Debug.Print Sum
Debug.Print ActiveSheet.Cells(i, 5).Value
If Sum > Target_sales Then
MsgBox "Target Achived by Customer " & ActiveSheet.Cells(i, 2).Value
Exit Do
End If
i = i + 1
Loop
If Sum > Target_sales Then
GoTo eXIT_LOOP
End If
i = i + 1
If i <= salesRange.Rows.Count Then
GoTo jump
End If
eXIT_LOOP:
End Sub
VBA Code Breakdown
Sub Exit_when_fixed_target_met()
Dim salesRange As Range
Set salesRange = Range("C5:C14")- Declares a variable salesRange as a range object.
- The Set statement assigns the range “C5:C14” to the salesRange variable.
Target_sales = ActiveSheet.Cells(5, 7).Value
Debug.Print Target_sales- Assigns the value of the cell at row 5, column 7 (G5) of the active sheet to the variable Target_sales.
- The Print statement is used to display the value of Target_sales in the Immediate window for debugging purposes.
Sum = 0
i = 5
jump:- Initialize the variables Sum and i.
- Sum is used to store the cumulative sum of sales.
- i is used as a counter to iterate through the rows.
Do While ActiveSheet.Cells(i, 3).Value = "Electronics"
Sum = Sum + ActiveSheet.Cells(i, 5).Value
Debug.Print Sum
Debug.Print ActiveSheet.Cells(i, 5).Value- Starts a “Do While” loop that continues as long as the value of the cell in the ith row and 3rd column of the active sheet is equal to “Electronics“.
- Inside the loop, the value of the cell in the ith row and 5th column is added to the variable Sum.
- The Print statements are used to display the current value of Sum and the value of the cell being added in the Immediate window for debugging purposes.
If Sum > Target_sales Then
MsgBox "Target Achieved by Customer " & ActiveSheet.Cells(i, 2).Value
Exit Do
End If
i = i + 1
Loop- Checks if the current sum is greater than Target_sales. If the condition is true, a message box is displayed indicating that the target has been achieved by the customer in the ith row of the 2nd column. Then the Exit Do statement is used to exit the loop prematurely.
- i is incremented by 1 in each iteration.
If Sum > Target_sales Then
GoTo eXIT_LOOP
End If
i = i + 1
If i <= salesRange.Rows.Count Then
GoTo jump
End If
eXIT_LOOP:
End Sub- Checks if the current sum, Sum, is greater than Target_sales after the loop has finished iterating through the rows.
- If the condition is true, the program jumps to the eXIT_LOOP label.
- i is incremented by 1.
- If i is still less than or equal to the number of rows in salesRange, the program jumps back to the jump label and continues iterating through the rows.
- The code reaches the eXIT_LOOP label at the end of the loop, signifying the end of the subroutine.
- After clicking Run the target sales achieved by the Customer name in a specific category are shown in the message box.
Method 3 – Exit a Do While Loop While Inside a For Loop
In this example, we will input a Do-While loop with an option to exit inside a For loop. Using this code structure, we can apply dual filter conditions to the values.
For example, in the dataset given below we have the Order id with the Customer Name, Order Status. We’ll first determine which of the data is in “Pending” status, then extract our desired person’s name from the same row.
- Paste the following code in the VBA code editor:
Sub Search_By_Filter()
Dim orderRange As Range
Set orderRange = Range("D5:D9")
For Each cell In orderRange.Rows
Do While cell.Value = "Pending"
i = i + i
If cell.Offset(0, -1).Value = "Mark Davis" Then
MsgBox "The Order Id is " & cell.Offset(0, -2).Value
End If
Exit Do
Loop
Next cell
End Sub
VBA Code Breakdown
Sub Search_By_Filter()
Dim orderRange As Range
Set orderRange = Range("D5:D9")- Declares a variable orderRange as a range object.
- The Set statement assigns the range “D5:D9” to the orderRange variable.
For Each cell In orderRange.Rows- Starts a loop that iterates through each row in the orderRange range. The variable cell represents the current cell in each iteration.
Do While cell.Value = "Pending"
i = i + i
If cell.Offset(0, -1).Value = "Mark Davis" Then
MsgBox "The Order Id is " & cell.Offset(0, -2).Value
End If
Exit Do
Loop- Starts a “Do While” loop that continues as long as the value of the current cell (Value) is equal to “Pending“.
- The variable i is incremented by itself in each iteration (i = i + i). However, this line seems to be incorrect and should be i = i + 1 to increment i by 1 in each iteration.
- Inside the loop, if the value of the cell in the column before (cell.Offset(0, -1).Value) is equal to “Mark Davis“, a message box is displayed, showing the Order ID from the cell two columns before (cell.Offset(0, -2).Value). The Exit Do statement is then used to exit the loop prematurely.
Next cell- Signifies the end of the loop and moves to the next cell in the orderRange.
- After running the code, the code will search for “Pending” in the status, then for the name “Mark Davis”
- If the code finds that person, it will show the message “The Order Id is: 3”
Method 4 – Exit a Do While Loop While Getting a Cumulative Value
In this example, we will determine in which month the total value of the sales record is broken. Suppose we have a sales value for each month in one column, and we’ll derive the cumulative sales values in another column. We can calculate the value of the target sales with the code below .
- Open the VBA editor and paste the following code in the code editor, then Run it.
Sub Cumulative()
Dim orderRange As Range
Set orderRange = Range("D5:D9")
target_sales = ActiveSheet.Cells(5, 6).Value
Debug.Print target_sales
counter = orderRange.Rows.Count
Debug.Print counter
i = 1
Debug.Print orderRange.Cells(i, 1).Value
Do While i < counter
Debug.Print i
If orderRange.Cells(i, 1).Value > target_sales Then
MsgBox "Target Achieved in the Month of " & orderRange.Cells(i, 1).Offset(0, -2).Value
Exit Do
End If
i = i + i
Loop
End Sub
VBA Code Breakdown
Sub Cumulative()
Dim orderRange As Range
Set orderRange = Range("D5:D9")- Declares a variable orderRange as a range object.
- The Set statement assigns the range “D5:D9” to the orderRange variable.
target_sales = ActiveSheet.Cells(5, 6).Value
Debug.Print target_sales- Assigns the value of the cell at row 5, column 6 (F5) of the active sheet to the variable target_sales.
- The Print statement is used to display the value of target_sales in the Immediate window for debugging purposes.
counter = orderRange.Rows.Count
Debug.Print counter- Assigns the number of rows in the orderRange to the variable counter.
- The Print statement is used to display the value ofcounter in the Immediate window.
i = 1
Debug.Print orderRange.Cells(i, 1).Value- Initializes the variable i to 1.
- The Print statement is used to display the value of the cell in the first row of orderRange in the Immediate window.
Do While i < counter
Debug.Print i- Starts a “Do While” loop that continues as long as the condition i < counter is true.
- The Print statement is used to display the value of i in the Immediate window for each iteration of the loop.
If orderRange.Cells(i, 1).Value > target_sales Then
MsgBox "Target Achieved in the Month of " & orderRange.Cells(i, 1).Offset(0, -2).Value
Exit Do
End If- Checks if the value of the cell in the ith row and 1st column of orderRange is greater than target_sales. If the condition is true, a message box is displayed indicating that the target was achieved in the corresponding month. The Exit Do statement is used to exit the loop prematurely.
i = i + i
Loop- Increments the value of i by itself in each iteration of the loop.
- The loop continues until the condition i < counter is no longer true.
After clicking the Run command, the month in which the target is achieved is displayed in a message box.
Exit the Loop If a Condition is Met in VBA
Here, we’ll demonstrate a VBA code that enables you to quit or exit the code whenever the specified conditions are met.
For example, suppose we have employee data with their total sales amounts listed. We also have the total sales value, and the sales criteria in cell F5.
- In the VBA code editor, paste the following code, and click on the Run command:
Sub ExitLoopExample()
Dim employeeData As Range
Dim employee As Range
Set employeeData = Range("B5:B9")
Target_Value = ActiveSheet.Cells(5, 6).Value
For Each employee In employeeData.Rows
Debug.Print employee.Offset(0, 2).Value
Dim salesAmount As Double
salesAmount = employee.Offset(0, 2).Value
If salesAmount >= Target_Value Then
MsgBox "Employee " & employee.Value & " Completed the sales target First!"
Exit For
End If
Next employee
MsgBox "Loop Exited Because Condition Met."
End Sub
VBA Code Breakdown
Sub ExitLoopExample()
Dim employeeData As Range
Dim employee As Range
Set employeeData = Range("B5:B9")- Declares two variables: employeeData and employee. employeeData is a range object that will represent the range “B5:B9” in the active sheet.
- The Set statement assigns the range “B5:B9” to the employeeData variable.
Target_Value = ActiveSheet.Cells(5, 6).Value- Assigns the value of the cell at row 5, column 6 (F5) of the active sheet to the variable Target_Value.
For Each employee In employeeData.Rows- Starts a loop that iterates over each row in employeeData. The employee variable represents the current row in each iteration.
Debug.Print employee.Offset(0, 2).Value- Prints the value of the cell that is two columns to the right of the current employee’s cell. The Print statement is used for debugging purposes to display information in the Immediate window.
Dim salesAmount As Double
salesAmount = employee.Offset(0, 2).Value- Declare a variable salesAmount as type Double and assigns the value of the cell that is two columns to the right of the current employee’s cell to salesAmount.
If salesAmount >= Target_Value Then
MsgBox "Employee " & employee.Value & " Completed the sales target First!"
Exit For
End If- This block checks if the salesAmount is greater than or equal to the Target_Value. If it is, a message box is displayed indicating that the employee has completed the sales target first. Then, the Exit For statement is used to exit the loop prematurely.
Next employee- This line signifies the end of the loop and moves to the next row in the employeeData range.
MsgBox "Loop Exited Because Condition Met."
End Sub- This line displays a message box indicating that the loop was exited because the condition was met. This line is executed either after the loop has finished iterating over all the rows in employeeData, or if the Exit For statement was encountered.
After clicking Run, the Employee who managed to hit the target first is displayed in the message box.
- Click OK on the message box.
The code will stop searching for the value in the worksheet and exit, as the conditions are met.
Things to Remember
Define the loop condition: Before entering the loop, make sure you have a clear understanding of the condition that needs to be met for the loop to continue. The Do While loop will execute as long as the specified condition is true.
Set an exit condition: Inside the loop, determine when to exit the loop using the Exit Do statement. The Exit Do statement allows you to terminate the loop prematurely and continue executing the code outside the loop. If there is any error or fault in placing the Exit condition, then the code might end up in an endless loop, which might crash the Excel software altogether.
Place Exit Do where necessary: You should strategically place the Exit Do statement within the loop based on your specific requirements. It is typically placed within an If statement to check a particular condition and exit the loop if it is met.
Perform necessary actions before exiting: If there are any tasks you need to perform before exiting the loop, make sure to include them before the Exit Do statement. This could involve updating variables, displaying messages, or any other actions required in your scenario.
Frequently Asked Questions
- How do you exit a loop in Excel?
In Excel, you can exit a loop by using the “Exit” statement. However, Excel does not have built-in loop constructs like other programming languages, such as “for” or “while” loops. Instead, you typically use loop structures in conjunction with conditional statements. To exit a For loop, use the Exit For statement. Or to exit a Do statement, use the Exit Do statement.
- How do you stop an infinite loop in a Do While?
To stop an infinite loop in a “Do While” loop, include a condition that will eventually evaluate to false. Without such a condition, the loop will continue indefinitely, resulting in an infinite loop.
- How do I stop a loop in VBA Macro execution Stuck?
Your VBA macro may get stuck in an infinite loop or a loop that is taking an excessively long time to execute. You will need to forcibly stop the macro’s execution. Here are a few methods you can try to stop a stuck loop in a VBA macro:
- Press “Ctrl + Break“: Can interrupt the execution of the macro and stop the loop.
- Use the “Stop” button in the VBA Editor: If you’re running the macro from the VBA Editor, you can click on the “Stop” button (a square-shaped icon) in the toolbar. This will halt the execution of the macro and stop the loop.
- Close the Excel application: If the above methods don’t work or if Excel becomes completely unresponsive, close the Excel application altogether. This will terminate the execution of the macro and stop the loop. Then reopen Excel and proceed without the stuck loop.
Get FREE Advanced Excel Exercises with Solutions!
