Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

Master VBA Loop in Excel - 3 Types of VBA LOOPs | MyExcelOnline Master VBA Loop in Excel - 3 Types of VBA LOOPs | MyExcelOnline

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 –

Download the Excel Workbook below to follow along and understand how VBA Loop works in Excel –
download excel workbook VBA-Loop-in-Excel.xlsm

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
See also  Print Excel header row and column on Every Page

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.

Master VBA Loop in Excel - 3 Types of VBA LOOPs

STEP 2: Click on Insert > Module.

Master VBA Loop in Excel - 3 Types of VBA LOOPs

STEP 3: Write the following code

Master VBA Loop in Excel - 3 Types of VBA LOOPs

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.
See also  How to Simplify Dates with DATEPART Function in Excel VBA

STEP 4: Press the Run icon.

Master VBA Loop in Excel - 3 Types of VBA LOOPs

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

Master VBA Loop in Excel - 3 Types of VBA LOOPs

 

#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.

Master VBA Loop in Excel - 3 Types of VBA LOOPs

STEP 2: Write the following code

Master VBA Loop in Excel - 3 Types of VBA LOOPs

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.
See also  VBA For Loops in Excel: Your Step-by-Step Guide

STEP 3: Press the Run icon.

Master VBA Loop in Excel - 3 Types of VBA LOOPs

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

Master VBA Loop in Excel - 3 Types of VBA LOOPs

 

#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.

Master VBA Loop in Excel - 3 Types of VBA LOOPs

STEP 2: Write the following code

Master VBA Loop in Excel - 3 Types of VBA LOOPs

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.
See also  4 Easy Methods to Convert Alphabet to Number in Excel

STEP 3: Press the Run icon.

Master VBA Loop in Excel - 3 Types of VBA LOOPs

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

Master VBA Loop in Excel - 3 Types of VBA LOOPs

 

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
Master VBA Loop in Excel - 3 Types of VBA LOOPs | MyExcelOnline Master VBA Loop in Excel - 3 Types of VBA LOOPs | MyExcelOnline
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.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...