How to Use For Loops in VBA

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In programming, loops allow you to run commands multiple times.

It’s simple, but it’s one of the most powerful things you can do with VBA code. (And it saves you a huge amount of typing.)

Let’s take a look at how loops save you time and give you great power over your Excel spreadsheet.

It’s easier to practice Visual Basic for Applications when you have some data to work with.

Download this example spreadsheet to follow along with the code we write later in the article!

There are two main parts of a for loop:

First is the counter. This counts how many times the loop has been executed (it also tells the computer how many times to execute the commands).

Second is a list of commands. It can be a single line of code or many, but each line in this section will be executed every time the loop runs.

Once the loop has run as many times as you’ve specified, it ends and your VBA code continues executing on the next line.

Here’s a simple for loop that we’ll use to check out the basic syntax:

Sub diag()
For i = 1 To 5
  Cells(i, i).Value = i
  Next
End Sub

To start a for loop, you’ll need a counter. That’s what “For i = 1 To 5” does. i is the variable that we’ll use for our counter.

The range we specify, 1 to 5, tells Excels how many times to run the loop. In this case, it will run the loop five times.

The first time it runs the loop, the variable will be set to 1. The next time, it will be set to 2. And so on.

Next, we write the lines of code that Excel will execute. In our case, we’ve identified a cell (at the coordinates (i, i)) and set the value equal to i.

Finally, we use Next to tell Excel that it’s reached the end of the loop. Then it will start over if the counter hasn’t maxed out.

Here’s the result:

The first time the loop runs, i is equal to 1. So the cell at (1, 1) is set to 1.

The next time, i is 2, so the cell at (2, 2) is set to 2.

And so on.

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.

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.

Here’s 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 outer loop first. Here’s what the outer loop 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 A2:A5. The next time, B2:B5, 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 outer 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.

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.

The idea of a loop is quite simple. But it can get complicated fast.

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!