How to Use “For Loops” in Excel VBA: Step-by-Step (2023)
In programming, loops allow you to run the same code multiple times.
It’s simple, but it’s one of the most powerful things you can do with VBA. (And it saves you a huge amount of typing.)
In this guide, I show you how to use the specific loop type called “For Loops” along with a few examples.
Download the sample Excel file here and try the practical examples for yourself.
Table of Contents
How to write a for loop in VBA
This is a For Loop in Excel VBA.
Sub loopexample() For i = 1 To 5 Cells(i, i).Value = i Next End Sub
There are two main parts of the above code:
- The counter
- The commands
First is the counter variable. This counts how many times the loop has been executed (it also tells the computer how many times to execute the commands).
The Second is a list of commands. It can be a single line of code or several, but each line in this section will be executed every time the loop runs.
Once the loop has run the specified number of times, it ends and your VBA code continues executing on the line below the VBA loop exit (the “Next” word you see in the above code).
To start a For Loop
Now, let’s continue with the VBA loop example from before.
Sub loopexample() For i = 1 To 5 Cells(i, i).Value = i Next End Sub
To start a for loop like the above example, you’ll need a counter variable for a numeric value.
That’s what “For i = 1 To 5” does.
i is the variable that we’ll use for our counter.
You don’t need to use i as the name of your variable; it can be anything you want.
But i is commonly used in programming, so we’ll stick with that convention.
How many times the code should run
The range we specify, “1 to 5”, tells Excel how many times the loop executes. In this case, it will run the loop five times.
The leftmost number is the starting value of the counter variable (i).
So, the first time the loop executes, i will be equal to 1.
The next time, it will be set to 2.
The third time, 3.
And so on…
The code executed in the loop
Next, we write the lines of code that Excel will execute.
From our example, it’s this code:
In our case, the Cells(i,i) code identifies a cell at the coordinates (i, i).
Yes, that’s the i from before. So we’re reusing our variable counter value to do something within the loop too. This is not required but is pretty fun and sometimes extremely handy 😊
And the .Value = i set the value equal to i.
Yes, we’re reusing the variable counter value AGAIN! 🤯
How to exit the loop
Finally, we use the Next word to tell Excel that it’s reached the end of the loop. Then it will start over if the counter hasn’t maxed out.
Because For always indicates the start of a For Loop, and Next always indicates the end, the For Loop is often called a For Next Loop.
When the counter reaches the desired counter value (in this case 5) the loop stops.
Here’s the result:
Understanding the result
The first time the loop runs, i is equal to 1. So cell A1 is set to 1.
Now, what’s the (1,1) or (i,i) way of writing cell addresses? 🤔
That’s just another method you can use to refer to cells with VBA code. (1,1) means row 1, column 1.
So (1,1) is A1. And (2,3) would be C2.
The next time the loop runs, i is 2, so the cell at (2,2) is set to 2.
And so on.
And the final value (5,5) is set to 5.
Pro Tip: Downloadable Excel template
If you don’t want to write your For Loop from scratch you can just download the sample Excel file here and use it as a loop Excel template for your next loops.
Other For Loop example
Here’s another example of a for loop:
Sub averageSales() For i = 2 To 21 newRange = Range(Cells(i, 2), Cells(i, 5)) Cells(i, 6).Value = Application.Average(newRange) Next End Sub
Open the example spreadsheet and go to the Visual Basic editor in the Developer tab. Or use the shortcut below:
Windows: Alt + F11
Mac: Fn + ⌥ + F11
Copy and paste the script above into a new module, and execute it one step at a time using F8. Watch as Excel steps through the script, and think about how the value of i changes what’s happening.
How to use nested for loops in VBA
To get even more out of a for loop, you can put another for loop inside of it. This is called nesting loops, and while it can be difficult to wrap your head around at first, it’s very powerful.
The below code is an example of nested loops that we can use on our example spreadsheet:
Sub quarterlyAverage() Dim breakStatus As Integer breakStatus = 0 For i = 2 To 21 newRange = Range(Cells(i, 2), Cells(i, 5)) For j = 2 To 5 Cells(i, j).Select If Cells(i, j) = 0 Then breakStatus = 1 Exit For Else breakStatus = 0 End If Next Range("J1").Value = breakStatus If breakStatus = 1 Then Cells(i, 6).Value = "Missing data" Else Cells(i, 6).Value = Application.Average(newRange) End If Next End Sub
Let’s break it down, one section at a time. We’ll look at the outside loop first. Here’s what it looks like:
For i = 2 To 21 newRange = Range(Cells(i, 2), Cells(i, 5)) If breakStatus = 1 Then Cells(i, 6).Value = "Missing data" Else Cells(i, 6).Value = Application.Average(newRange) End If Next
Excel counts from 2 to 21, and looks at cells in the range of (i, 2) to (i, 5). On the first time through, that will be B1:E1. The next time, B2:E2, and so on.
Then it checks a variable called “breakStatus.” If that variable is set to 1, then Excel writes “missing data” in column F.
(This tells us that one of the sales figures is missing.)
If breakStatus isn’t set to 1, it takes the average of the cells in the range we established before.
That’s it. It’s pretty simple.
Now let’s look at the inner loop:
For j = 2 To 5 If Cells(i, j) = 0 Then breakStatus = 1 Exit For Else breakStatus = 0 End If Next
This creates a new counter, j, and looks at individual cells in each loop. In the first run through the outside loop, i is set to 1. So the first run of the inner loop will look at (i, j), which is (1, 2), or A2.
Excel checks to see if that cell is set to 0, and if it is, it sets breakStatus to 1 and exits the loop (that’s what “Exit For” does; see below).
If that cell isn’t set to 0, the loop continues. On the next run through the inner loop, j will be set to 3, so Excel looks at A3 to see if it’s set to 0.
Loop Excel VBA break: How to exit loops
A loop VBA break refers to exiting loops early using the VBA-code: “Exit For”.
It’s great for stopping the code if some condition has been met.
You’ll almost always want to use it as part of an if statement, as we have here in the above code.
It’s important to note that loop VBA break only exits one loop. In our case, it stops the inner loop from running again. But the outer loop continues as normal.
Run the script on the example spreadsheet one step at a time by pressing F8.
You’ll be able to see Excel stepping through the loops, which should give you more insight into how the script works.
Changing the step value (step 2, etc)
Take a look at the below code:
Sub stepvalueexample() For i = 1 To 20 Cells(i, 1).Value = 5 * i Next End Sub
It puts 5 times i (and i starts at 1) in cell A1 and goes down to the rest of column A multiplying i with 5.
It does so for all the cells until the step counter value reaches 20.
So, 20 times.
If you want the counter to skip some values, you can add the Step X code to do so. Like this:
For i = 1 To 20 Step 2 Cells(i, 1).Value = 5 * i Next
The “Step 2” code is added after the “1 to 20” part.
This means that every other i value will simply be skipped by the loop.
In this case, it means if i = 2, 4, 6, 8, etc, it won’t be included.
That’s it – Now what?
The idea of a loop is quite simple.
But what about a loop inside a loop inside a loop (that’s 3 loops)? Now it starts to get complicated really quickly 🤯
With practice, you’ll develop a sense of how loops work and interact. It can be frustrating at first but stick with it, and it’ll become much more intuitive!
Want to learn more about VBA loops and other important VBA topics?
Then sign up for my free 30-minute VBA training course here.
The “For Loop” is not the only VBA loop type out there. There are other loops!
The “Do Loop” is also pretty great for a specific set of purposes, especially the “Do While” loop that loops any number of times but with a certain condition. Get started with “Do Loops” here.