VBA Do Loop | How to use Do Loop in VBA? (with Examples)

Excel VBA Do Loop

VBA Do While loop works on logical results, it keeps running the loop back and forth while the test condition is TRUE, the moment test condition returns FALSE it will exit the loop.

Loops are the heart of any programming language. In our articles, we demonstrate the importance of loops and ways of coding them. In this article, we are showing you how to use Do Loop.

How to use the VBA Do Loop?

You can download this VBA Do Loop Excel Template here – VBA Do Loop Excel Template

Example #1 – Condition at End of Loop

We have seen the condition test at the beginning of the loop. In the earlier code, we have seen the example of inserting serial numbers and code was like that.

Code:

Sub Do_While_Loop_Example1()
    Dim k As Long
    k = 1
    Do While k <= 10
       Cells(k, 1).Value = k
       k = k + 1
    Loop
End Sub

Now you can run this code manually or through shortcut key F5, to see the result.

This code will insert serial numbers from 1 to 10.

But we can also test the condition at the end of the loop as well. For this, we need to use the word “while” and the condition test at the end after the word Loop.

The only change here is to apply the test at the end as shown below.

Code:

Sub Do_While_Loop_Example1()
    Dim k As Long
    k = 1
    Do
      Cells(k, 1).Value = k
      k = k + 1
    Loop While k <= 10
End Sub

Like this, we can test the condition at the end of the loop statement as well.

Note: Code will run then it tests the condition to go back to loop one more time or not. This means it will run first and then test the condition later.

Example #2 – Summation Using Do While Loop

Assume you have sales and cost data in your excel sheet. Below is the set of dummy data I have created for the purpose of calculation.

Now we need to get the value of profit in column C. I have already created a code that will do a job for me.

Code:

Sub Do_While_Loop_Example2()
    Dim k As Long
    Dim LR As Long
    k = 2
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Do While k <= LR
       Cells(k, 3).Value = Cells(k, 1) + Cells(k, 2)
       k = k + 1
    Loop
End Sub

LR = Cells(Rows.Count, 1).End(xlUp).Row

This code will identify the last used row in the first column. This makes the code dynamic because if there is any addition or deletion of the data this will adjust my sequence time to run the loop.

k = 2

We want the calculation to be done from the second cell onwards. So k’s initial value is 2.

Do While k <= LR

As I told LR will find the last used row in the first column. This means the loop will run while k is <= to the value of LR. In this case, I have 10 rows, so LR = 10.

Loop will run until the k value reaches 10, once the value has passed 10 loops, it will stop.

Now you can run this code using shortcut key F5 or manually, to see the result.

Example #3 – Exit Statement in Do While Loop

We can also exit the loop while the condition is still TRUE only. For example, take the above data here as well.

Assume you don’t want to do the full calculation but you only need to calculate the first 5 rows of profit and as soon as it reaches the 6^th row, you want to come out of the loop. This can be done by using the IF function, below code, includes the exit statement.

Code:

Sub Do_While_Loop_Example3()
    Dim k As Long
    Dim LR As Long
    k = 2
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    Do While k <= LR
    If k > 6 Then Exit Do
       Cells(k, 3).Value = Cells(k, 1) + Cells(k, 2)
       k = k + 1
    Loop
End Sub

“If k > 6 Then Exit Do”

This line of code will initiate the exit process. Loop will keep running until the value of k reaches 6, the moment it exceeds 6 If condition will execute the code “Exit Do”.

Now you can run this code using shortcut key F5 or manually, to see the result.

Things to Remember

Do While is the loop works on logical results, it keeps running the loop back and forth while the test condition is TRUE, the moment test condition returns FALSE it will exit the loop.
We can exit the loop at any given point of time by adjusting one more logical test inside the loop by using the IF function.
If the condition or test supplied at the top of the loop, it will first check the test and progress further only if the result is TRUE.
If the condition or test supplied at the end of the loop, it will first execute the block of code inside the loop statement and at the end, it will test the condition to decide to whether to go back to run the loop one more time or not.

Excel VBA Do Loop

How to use the VBA Do Loop?

Example #1 – Condition at End of Loop

Example #2 – Summation Using Do While Loop

Example #3 – Exit Statement in Do While Loop

Things to Remember

Recommended Articles