How to Use Excel VBA Do While Loop with Multiple Conditions

The Do While loop in Excel VBA allows code to be executed repeatedly as long as a specified condition remains true. In this article, we will explore how to incorporate multiple conditions into a Do While loop, covering the syntax, logical operators, and best practices to create efficient and flexible loops to handle complex conditions.

Syntax of Do While Loop in VBA

Do While – The loop keyword is used to start the loop. It specifies that the loop will be executed while a certain condition is true.

Condition – The condition that determines whether the loop should continue or exit. It is a logical expression that evaluates to either True or False. If the condition is True, the loop body is executed. If it’s False, the loop is exited.

Statements – The code that is executed repeatedly as long as the condition is true. This can be any valid VBA code, including assignments, calculations, or function calls.

Loop – The keyword used to mark the end of the loop. After the Loop statement, the program returns to the Do While statement to check the condition again.

Do While
 <condition>
    <statements>
Loop

Excel VBA Do While Loop with Multiple Conditions: 3 Suitable Approaches

Example 1 – Finding Total Amount

Let’s sum numbers from 1 to 10 by using VBA Do While Loop with multiple conditions.

Steps:

Click here to see how to launch VBA and insert a Module in Excel.
After launching the VBA and inserting a module, copy the following code and paste it into the module:

Sub ExampleDoWhileLoop()
    Dim i As Integer
    Dim total As Integer
    i = 1
    total = 0
    Do While i <= 10 And total + i < 50
        total = total + i
        i = i + 1
    Loop
    MsgBox "The total is: " & total
End Sub

We set two conditions: i<=10 and total+i<50. Valid results are between 10 and 50.

Press F5 or click on on the Play button to run the code.

The output is 45.

The sum of 1 to 10 should be 55 but because of the second condition, the outcome is 45.

Example 2 – Coloring Multiple Cells

Suppose we have a dataset that includes the Student’s Name, Student ID, and Number. We will format the cells with color based on whether the Number is greater than or equal to 80.

Steps:

Launch VBA and insert a Module.
Enter the following code in the module:

Sub Do_While_Loop2()
Dim i As Integer
i = 5
Do While i <= 13
If Range("D" & i).Value >= 80 Then
Range("D" & i).Interior.Color = RGB(204, 255, 0)
End If
i = i + 1
Loop
End Sub

Click on the Play button or press F5 to run the code.

Only the cells with values greater than 80 are highlighted in yellow..

Example 3 – Using an IF Statement in a Do While Loop for Multiple Layers of Conditions

In this example, we’ll find the even numbers within a range by using the Do While loop with multiple conditions including an IF statement.

Steps:

Launch VBA and insert a Module.
In the Module paste the following code:

Sub FindEvenNumbers()
    Dim i As Integer
    Dim evenNumbers As String
    i = 1
    Do While i <= 10
        If i Mod 2 = 0 Then
            evenNumbers = evenNumbers & " " & i
        End If
        i = i + 1
    Loop
    MsgBox "Even numbers in the range:  " & evenNumbers
End Sub

Run the code by pressing the F5 key or by clicking on the Play button.

A message box opens displaying the even numbers between 1 and 10.

How to Use a Do While Loop in VBA

We can of course also use the Do While Loop with just a single condition. For example, let’s use it to find out the total numbers from 1 to 10. We will input only criteria here while writing the code.

Steps:

Launch the VBA and insert a Module.
Paste the following code in the Module window:

Sub ExampleDoWhileLoop()
    Dim i As Integer
    Dim output As String
    i = 1
    Do While i <= 10
        ' Concatenate the output with current i value
        output = output & " " & i
        ' Increment the i
        i = i + 1
    Loop
    ' Display the output in a message box
    MsgBox output
End Sub

Click on the Play button or press F5 to run the code.

A message box opens displaying all the numbers from 1 to 10.

Things to Remember

Syntax: Ensure that you follow the correct syntax for the Do While loop in VBA. The loop starts with the Do While statement, followed by the condition, and ends with the Loop statement.
Condition order: The conditions should be arranged in a logical order that makes sense for your program’s requirements.
Exit condition: Make sure to include an exit condition in your loop. The loop can run indefinitely without an exit condition, causing your program to become unresponsive or result in an infinite loop.
Logical operators: Use logical operators such as And, Or, and Not to combine or negate conditions as needed. These operators help you create complex conditions by evaluating multiple expressions.
Condition evaluation: Ensure that the conditions in the Do While loop are properly evaluated. Double-check the logical operators, relational operators, and variables used in the conditions to ensure they are correctly implemented.
Testing and debugging: It’s recommended to test and debug your code to verify that the loop conditions and code execution are working as expected. Step through the code using breakpoints or add debug output to ensure the loop behaves as intended.

Frequently Asked Questions

How do I use multiple If conditions in VBA?

Combine the VBA IF statement with the AND operator. You can then test two conditions at once and determine whether both of them are True. The result is returned as False if only one of the conditions is True.

How many times can I execute a While loop?

As long as the condition remains True, the loop will continue to execute. There is no inherent limit on the number of iterations you can have in a Do While loop.