How to Use “For Loops” in Excel VBA: Step-by-Step (2024)

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.

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.

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

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.

Other resources

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.