Master VBA Loop in Excel – 3 Types of VBA LOOPs

Excel is an exceptional tool for data management, complex calculation, and data visualization. However, when it comes to tackling repetitive tasks and elevating Excel to the next level, VBA comes to the rescue. VBA loop allows you to repeat the same task over a range of cells until a specific point is attained or a given condition is met.

In this article, we will cover the following topics –

Introduction to VBA Loop

VBA (Visual Basic for Applications) is a very valuable and powerful programming language in Excel. The VBA loop enables you to repetitively execute a task across a range of cells until a specific condition is reached or a given criterion is satisfied.

VBA Loop can be used to automate your work with the help of just a few lines of code. By sparing you from writing repetitive code and offering flexibility, it streamlines your work processes. It also helps you save time and eliminates the potential for human errors.

There are 3 main types of VBA loops in Excel –

• For Loop: Using a counter to run the code for a specified number of times
• Do Until Loop: Looping until a condition is True
• Do While Loop: Looping while a condition is True

Let’s look at each of these types.

#1- For Loop

The For Loop is used when you know the exact number of times you want the loop to run. It is ideal for iterating over a range of cells or performing calculations a specific number of times.

The syntax of the loop includes a counter variable that begins with the starting value and repeats through the loop until it reaches the end value specified by the user.

In this example, we will use a For Loop in Excel VBA to insert serial numbers 1 to 10 in cells A1 to A10. The loop will run from 1 to 10, and each value will be inserted into the respective cells. Follow the steps below –

STEP 1: Open the Workbook and press Alt + F11 to open the VBA editor.

STEP 2: Click on Insert > Module.

STEP 3: Write the following code –

Sub InsertSerialNumbers()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = i
Next i
End Sub

• Sub InsertSerialNumbers() – This line is used to define the VBA with the name “InsertSerialNumbers()”.
• Dim i As Integer – We declare a variable “i” as an integer to act as the counter variable.
• For i = 1 To 10 – The For Loop starts with “i” set to 1 and continues until “i” reaches 10.
• Cells(i, 1).Value = i – Cell is used to specify the row and column. In this case, we set the value of the cell in column A (column number 1) and the row number indicated by the value of “i.”
• Next i – This marks the end of the loop and once Excel reaches this line the value of i is increased by 1.

STEP 4: Press the Run icon.

Serial numbers ranging from 1 to 10 have been successfully added to cells A1 to A10.

#2 – Do Until Loop

The Do Until loop will keep repeating its execution until the specified condition is met i.e. it evaluates to be TRUE. As long as the condition remains false, the Do Until statements will continue to be executed. Once the condition is TRUE, the loop will end.

In this example, we will use a Do Until Loop in Excel VBA to insert serial numbers 11 to 20  in cells B1 to B10.

STEP 1: Click on Insert > Module.

STEP 2: Write the following code –

Sub InsertSerialNumbers()
Dim i As Integer
i = 11
Do Until i > 20
Cells(i - 10, 2).Value = i
i = i + 1
Loop
End Sub

• i = 11 – We assign the value 11 to “i” before entering the loop.
• Do Until i > 20 – The loop begins here with the starting value as 1 and keeps running until the value is greater than 10.
• Cells(i – 10, 2).Value = i – Cell is used to specify the row and column. In this case, we set the value of the cell in column B (column number 2) and the row number indicated by the value of “i -10.” In the 1st iteration, this line will be Cells(1,2).Value = 11.
• i = i + 1 – Once Excel reaches this line, the value of i is increased by 1. After the 1st iteration, it will become 12.
• Loop – This marks the end of the loop.

STEP 3: Press the Run icon.

Serial numbers ranging from 11 to 20 have been successfully added to cells B1 to B10.

#3 – Do While Loop

The Do While loop will keep repeating its execution while the specified condition is met. As long as the condition remains true, the Do While statements will continue to be executed. Once the condition is FALSE or is not met, the loop will end.

In this example, we will use a Do While Loop in Excel VBA to insert serial numbers 3 to 30  in cells C1 to C10, where each number is a multiple of 3.

STEP 1: Click on Insert > Module.

STEP 2: Write the following code –

Sub InsertMultipleOfThree()
Dim i As Integer
Dim j As Integer
i = 3
j = 1
Do While i <= 30
Cells(j, 3).Value = i
i = i + 3
j = j + 1
Loop
End Sub

• i = 3 and j = 1 – We assign the value 3 to “i” and 1 to “j” before entering the loop.
• Do While i <= 30 – The loop begins here with the starting value as 3 and keeps running while the value is less than or equal to 30.
• Cells(j, 3).Value = i – Cell is used to specify the row and column. In this case, we set the value of the cell in column C (column number 3) and the row number indicated by the value of “j” In the 1st iteration, this line will be Cells(1,3).Value = 3.
• i = i + 3 and j = j + 1 – Once Excel reaches this line, the value of i is increased by 3 and the value of j is increased by 1. After the 1st iteration, i will become 6 and j will become 2.
• Loop – This marks the end of the loop.

STEP 3: Press the Run icon.

The multiples of 3 ranging from 3 to 30 have been successfully added to cells C1 to C10.

Conclusion

The article explains how VBA loops in Excel help automate repetitive tasks and elevate data management. It covers three main types of loops: For Loop, Do Until Loop, and Do While Loop. The step-by-step examples illustrate how each loop type works.

Click here to learn more about For Loop in Excel.

If you like this Excel tip, please share it

email

Pinterest

Facebook

Twitter

Linkedin

John Michaloudis

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.