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

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.

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.

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

• The inputbox asking for the Student ID is shown below.

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

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

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

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 Achieved 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

• 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.

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

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

• 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”

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

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

• After clicking the Run command, we can see the month in which the target is achieved is now showing in the message box.

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

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

• 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.

• Now the code will stop searching for 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, 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.