VBA Excel to Exit Do While Loop (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Do While conditional loop in Excel works like almost If conditional Statement, with a bit more flexibility. For example, the if condition inside the Do While gives the user the option to apply a filter in multiple cases. With Exit Do command inside the Do While enables the user to escape the code structure when necessary. In this article, using VBA, we have shown how in Excel users can use the Exit Do command inside the Do While loop. A sample example of using the Exit Do command inside the Do While loop is given below.

Here we tried to extract the Order Id that belongs to a specific Customer Name and Order Status.

vba excel exit do while loop


Download Practice Workbook

Download the following workbook to practice by yourself.


VBA Excel to Exit Do While Loop: 4 Suitable Examples

Below, 4 separate examples of  how to Exit the Do While in Excel VBA in various circumstances are shown. User needs to understand each of the examples and then apply them according to their needs. In order to avoid any kind of incompatibility issue, try to use the Excel 365 edition.


1. Exit Do While Loop When Specific Marks of a Student Found in Excel VBA

In this example, we will try to find the student’s marks based on the student’s id and subject using the do-while conditional loop. Using the Exit Do command, whenever we found our desired value, we can exit the loop.

  • An example of this function is given below, for this, we need to open the VBA code editor window. For this users can use the helper article given here.
  • Then in the code editor, enter the following code, and 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 clicking on the Run command, we can see that there is an inputbox asking for the student’s id, after entering the id.

student details from DO while and Exit do commands in Excel VBA

VBA Code Breakdown

Sub GetStudentDetails()
    Dim studentMarks() As Variant
    studentMarks = Range("B4:E19").Value
  • This line declares studentMarks as a variant array to store the values of a range.
  • The 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 are 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 (number of rows).
  • Inside the loop, it checks if the student ID in the ith row and the 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 set to True to indicate that a match was found.
  • The Exit Do statement is used to exit the loop prematurely.
  • 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, it displays a message box indicating that the student ID and subject were not found in the dataset.
  • The inputbox asking for the Student ID is shown below.

InputBox asking the Studnet Id

  • There is another inputbox asking for the subject name. Enter your desired subject name here.

Inputbox asking the subject

  • After entering the Student id and Subject name, you can see that the student’s marks are now showing in the message box.

Messegebox showing the Mark of the student according to the Student Id and Subject


2. Exit Do While Loop When a Fixed Target of Sale Met

In this code, we will see how we can determine to which customer selling products we achieved our sales target. For this, we showed a sample code below.

  • For this, we need to open the VBA code editor following the helper article given above.
  • And then paste the code given below in that editor, and click on the Run command.
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 to return customer id who fulfill fixed target sale using Do while loop and Exit Do in Excel

VBA Code Breakdown

Sub Exit_when_fixed_target_met()
    Dim salesRange As Range
    Set salesRange = Range("C5:C14")
  • This line 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
  • This line 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:
  • These lines 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
  • This block 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, it adds the value of the cell in the ith row and 5th column 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
  • This block 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.
  • 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
  • This block 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, signifying the end of the subroutine.
  • After clicking Run you can see that the target sales achieved by the Customer name in a specific category are now showing in the message box.

Messgebox showing the customer id that fulfilled the target sales


3. Exit Do While Loop When Inside of a For Loop

In this example, we will try to input a do-while loop with an option to exit inside a For loop in Excel VBA. Using this code structure, we can apply dual filter conditions to the values.

For example in the dataset given below, where we have the Order id with the Customer Name, Order Status. We need first to determine which of the data is in “Pending” status. Then we will extract our desired person’s name from the same row.

  • Now open the VBA code editor from the helper article given above.
  • Then paste the following code
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

Do while with Exit do inside the For loop to return order Id in Excel vba

VBA Code Breakdown

Sub Search_By_Filter()
    Dim orderRange As Range
    Set orderRange = Range("D5:D9")
  • This line 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
  • This line 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
  • This block 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, it checks if the value of the cell in the column before (cell.Offset(0, -1).Value) is equal to “Mark Davis”.
  • If the condition is true, 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 used to exit the loop prematurely.
   Next cell
  • This line signifies the end of the loop and moves to the next cell in the orderRange.
  • After running the code, you will see the code will search for the Pending in the status.
  • Then will search for the person’s name as “Mark Davis
  • If the code manages to find that person, it will show the message “The Order Id is: 3”

Messegebox showing the Order Id


4. Exit Do While Loop When Getting Cumulative Value

In this example, we will try to determine in which month the total value of the sales record is broken. For this, we have sales value for each month. And then we get the cumulative sales values in the other column. We can get the value of the target sales from the code given below where we will exit a do while loop in Excel VBA.

  • For this, we first need to open the VBA editor from the article given above.
  • Then we need to paste the following code in the code editor. And click on the Run command.
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 to return month where Sales target met

VBA Code Breakdown

Sub Cumulative()
    Dim orderRange As Range
    Set orderRange = Range("D5:D9")

This line 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
  • This line 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
  • This line assigns the number of rows in the orderRange to the variable counter.
  • The Print statement is used to display the value of the counter in the Immediate window.
    i = 1
    Debug.Print orderRange.Cells(i, 1).Value
  • This line 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
  • This line 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
  • This block 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
  • This line 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, we can see the month in which the target is achieved is now showing in the message box.

messegebox showing the month where the target met


VBA Exit Loop If Condition Met in Excel

Here, we are going to demonstrate a VBA code. Using this you will be able to quit or exit the code whenever the specified conditions are met.

For example, we have the employee data with their total sales amount listed. We also get the total sales value. Now we also have the sales criteria in cell F5.

  • For using this code, we first need to open the Visual Basic Editor in Excel. For this, you can follow this helper article given here.
  • Then in the 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 to return employee name when condition met

VBA Code Breakdown

Sub ExitLoopExample()
    Dim employeeData As Range
    Dim employee As Range
    Set employeeData = Range("B5:B9")
  • This line 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

This line 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
  • This line starts a loop that iterates over each row in the employeeData. The employee variable represents the current row in each iteration.
Debug.Print employee.Offset(0, 2).Value
  • This line 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
  • These lines declare a variable salesAmount as a Double and assign 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 after the loop has finished iterating over all the rows in employeeData or if the Exit For statement was encountered.
  • After clicking Run you will notice that the Employee who managed to hit the target first are now showing in the message box. Click OK on the message box.

messee=gebox showing the Employee name when sale criteria met

  • Now the code will stop searching for value in the worksheet and exit as the conditions are met.

Messegebox showing that the Code exited as the conditions 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, you need to 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 while 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, you can use the Exit For statement. Or to exit a Do statement, you can 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, you need to include a condition that will eventually evaluate to false. Without such a condition, the loop will continue indefinitely, resulting in an infinite loop. Here’s an example of how you can stop an infinite loop in a “Do While” loop. In this case the main source or the dataset need to have such value and condition inside the code that the code must stop in any point of executing the code.

  • 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. Then, you may 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“: Pressing the “Ctrl” key on your keyboard and simultaneously pressing the “Break” key (also labeled as “Pause” or “Pause/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, you can close the Excel application altogether. This will terminate the execution of the macro and stop the loop. You can then reopen Excel and proceed without the stuck loop.

Conclusion

In this article, we have seen in VBA Excel, how we can exit the do while loop in different situations. User needs to understand the Examples and use them according to their own need. If you need any more assistance regarding Excel-related problems, then you should really check out the Exceldemy site.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo