VBA Do Loop

Excel VBA Do 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 in VBA.

How to Use VBA Do Loop in Excel?

Following are examples of VBA Do Loop.

VBA Do Loop 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.

VBA Do Loop 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.

VBA Do Loop 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 About VBA Do Loop

• 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 back to run the loop one more time or not.

Recommended Articles

This has been a guide to VBA Do Loop. Here we discuss how to use Do Loop in VBA including 1) Condition at End of Loop, 2) Summation Using Do While Loop, 3) Exit Statement in Do While Loop along with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

• Create VBA Login User Form
• VBA Replace Function to String
• CLng Function in VBA
• VARTYPE Function in VBA
• Break For Loop in VBA
• For Each Loop in VBA
• Do Until Loop in VBA
• For Next Loop in VBA