VBA Do Loop

Excel VBA Do Loop

VBA Do loop, it is a set of instructions inside a sub procedure where code runs a specific number of times until the desired criteria is reached or any threshold is exceeded or safe to say that until a required data is obtained.

While the loop works on logical results, it keeps running the loop back and forth while the test condition is TRUE. The moment the 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?

Example #1 – Condition at the 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 the 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. 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.

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 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 amount 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 in excel. The 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. The loop works on logical results, and it keeps running the loop back and forth while the test condition is TRUE. The moment the test condition returns FALSE, it will exit the loop.
• We can exit the loop at any given time by adjusting one more logical test inside the circle by using the IF function.
• If the condition or test is supplied at the top of the loop, it will first check the test and progress further only if it is TRUE.
• If the condition or test is supplied at the end of the loop, it will first execute the block of code inside the loop statement, and in the future, it will test the condition to decide whether to go 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 VBA Do Loop, including 1) Condition at the end of Loop, 2) Summation using Loop, 3) Exit Statement in Loop along with practical examples, and a downloadable excel template. Below you can find some useful excel VBA articles –

• VBA DoEvents
• VBA Login User Form
• For Each Loop in VBA
• For Next Loop in VBA
• VBA PowerPoint