VBA For Loop – A Complete Guide

VBA For Loop

“History is about loops and continuums” – Mike Bidlo.

This post provides a complete guide to the VBA For Loop, the VBA For Each Loop.

If you are looking for information about the VBA While and VBA Do Loop then go here.

If you want some quick info about the For loops then check out the Quick Guide table in the first section.

If you are looking for information on a particular topic then check out the Table of Contents below(if not visible then click on the post title).

 
 

A Quick Guide to VBA Loops

Loop formatDescriptionExample
For ... NextRun 10 timesFor i = 1 To 10
Next
For ... NextRun 5 times. i=2,4, 6 etc.For i = 2 To 10 Step 2
Next
For ... NextRun in reverse orderFor i = 10 To 1 Step -1
    Debug.Print i
Next
For ... NextGo through CollectionFor i = 1 To coll.Count
    Debug.Print coll(i)
Next
For ... NextGo through arrayFor i = LBound(arr) To UBound(arr)
    Debug.Print arr(i)
Next i
For ... NextGo through 2D arrayFor i = LBound(arr) To UBound(arr)
    For j = LBound(arr,2) To UBound(arr,2)
        Debug.Print arr(i, j)
    Next j
Next i
For Each ... NextGo through CollectionDim item As Variant
For Each item In coll
    Debug.Print item
Next item
For Each ... NextGo through arrayDim item As Variant
For Each item In arr
    Debug.Print item
Next item
For Each ... NextGo through 2D arrayDim item As Variant
For Each item In arr
    Debug.Print item
Next item
For Each ... NextGo through DictionaryDim key As Variant
For Each key In dict.Keys
    Debug.Print key, dict(key)
Next key
Both typesExit LoopFor i = 1 To 10
    If Cells(i,1) = "found" Then
        Exit For
    End If
Next i

 
 

Introduction

Loops are by far the most powerful component of VBA. They are the rocket fuel of your Macros. They can perform tasks in milliseconds that would take humans hours. They also dramatically reduce the lines of code your applications need.

If you have never used loops before then this post is a great place to start. It provides and in-depth guide to loops, written in plain English without the jargon.

Let’s start with a very important question – what are loops and why do we need them?

 
 

What are Loops and Why Do You Need Them?

A loop is simply a way of running the same lines of code a number of times. Obviously running the same code over and over would give the same result.

So what is important to understand is that the lines of code normally contain a variable that changes slightly each time the loop runs.

For example, a loop could write to cell A1, then cell A2, A3 and so on. The slight change each time is the row.

Let’s look at a simple example.

 
 

Example 1: Printing 1 to 5

 
The following code  prints the values 1 to 5 in the Immediate Window(Ctrl + G to view).

Debug.Print 1
Debug.Print 2
Debug.Print 3
Debug.Print 4
Debug.Print 5

 
 

The Immediate Window

If you have not used the Immediate Window before then this section will get you up to speed quickly.

The function Debug.Print writes values to the Immediate  Window. To view this window select View->Immediate Window from the menu( the shortcut is Ctrl + G)

 
 
ImmediateWindow

 
 
 
ImmediateSampeText

 
 

Example 2: Printing  1 to 20

Now imagine we want to print out the numbers 1 to 20. We would need to add 15 more lines to the example above.

 
 
However, using a loop we only need to write Debug.Print once.

    For i = 1 To 20
        Debug.Print i
    Next i

 
 
The output is

VBA Excel

Output

 
 
If we needed print the numbers 1 to 1000 then we only need to change the 20 to 1000.

Normally when we write code we would use a variable instead of a number like 20 or 1000. This gives you greater flexibility. It allows you to decide the number of times you wish to run the loop when the code is running. The following example explains this.

 
 

Example 3: Counting Fruit Sold

A common task in Excel is read all the rows with with data. 

 
The way you approach this task is as follows

  1. Find the last row with data
  2. Store the value in variable
  3. Use the variable to determine how many times the loop runs

 
 
Using a variable in the loop makes your code very flexible. Your will work no matter how many rows there are.

Let’s have a look at an example. Imagine you receive a sheet with a list of fruit types and their daily sales. You want to count the number of Oranges sold and this list will vary in size depending on sales.

 
 
The following screenshot shows an example of this list

Sample Data of Fruit Sales

Sample Data of Fruit Sales

 
 
We can use the code to count the oranges

Sub CountFruit()

    ' Get the last row with text
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Dim i As Long, Total As Long
    ' Use LastRow in loop
    For i = 2 To LastRow
        ' Check if cell has text "Orange"
        If Cells(i, 1).Value = "Oranges" Then
            ' Add value in column B to total
            Total = Total + Cells(i, 2).Value
        End If
    Next i

    ' Print total
    Debug.Print "Total oranges sold was:"; Total

End Sub

 
 
You can try this code for yourself. Change the number of fruit items and you will see that the code still works fine.

If you were to increase the number fruit items to a large value like 10,000 then you will hardly notice the difference in the time it takes to run – almost instantly.

Loops are super fast. This is what makes them so powerful. Imagine performing a manual task on 10,000 cells. It would take a considerable amount of time.

 
 

Advantages of For Loops

To conclude this section we will list the major advantages of using loops

  • They reduce the lines code you need
  • They are flexible
  • They are fast

 
 
In the next sections we will look at the different types of loops and how to use them.

 
 

The VBA For Loop

The VBA For loop is the most common loop you will use in Excel VBA. The For Loop is used when you can determine the number of times it will be run. For example, if you want to repeat something twenty times.

 
 

Format of the Loop

The VBA For Loop is constructed as follows

For <variable> = <start value> to <end value>
Next <variable>

The start and end values can be variables. Also the variable after Next is optional but it is useful and it makes it clear which for loop it belongs to.

 
 

How a Loop Works

Let’s look at a simple for loop that prints the numbers 1 to 3

    Dim i As Long
    For i = 1 To 3
        Debug.Print i
    Next i

 
 
How this code works is as follows

i is set to 1
The value of i(now 1) is printed

 
i is set to 2
The value of i(now 2) is printed

 
i is set to 3
The value of i(now 3) is printed

 
 
If we did not use a loop then the equivalent code would be

    Dim i As Long
    i = i + 1
    Debug.Print i
    i = i + 1
    Debug.Print i
    i = i + 1
    Debug.Print i

 
 
The i = i + 1 line is used to add 1 to i and is a common way in programming to update a counter.

 
 

Using Step

You can see that i is increased by one each time. This is the default. You can specify this interval using Step.

 
 
The next example shows you how to do this

    ' Prints the even numbers i.e. 2,4,6,8 ... 20
    For i = 2 To 20 Step 2
        Debug.Print i
    Next i

 
 
You can use a negative number with Step which will count in reverse

    ' Prints the even numbers in reverse i.e. 20,18,16,14 ... 2
    For i = 20 To 2 Step -2
        Debug.Print i
    Next i

 
 
Note: if Step is positive then your starting number must be lower than you ending number. The following loop will not run because the starting number 20 is greater than 10. VBA therefore, thinks it has already reached the target value 10.

    ' Will not run as starting number already greater than 10
    For i = 20 To 10 Step 1
        Debug.Print i
    Next i

 
 
If Step is negative then the start number must be greater than the end number.

 
 

Exit For

Sometimes you may want to leave the loop earlier if a certain condition occurs. For example if you read bad data.

 
 
You can use Exit For to automatically leave  the loop as shown in the following code

    For i = 1 To 1000

        ' If cell is blank then exit for
        If Cells(i, 1) = "" Then
            MsgBox "Blank Cell found - Data error"
            Exit For
        End If

    Next i

 
 

Using the For Loop with a Collection

The For loop can also be used to read items in a Collection.

 
 
In the following example, we display the name of all the open workbooks

    Dim i As Long
    For i = 1 To Workbooks.Count
        Debug.Print Workbooks(i).FullName
    Next i

 
 

Using Nested For Loops

Sometimes you may want to use a loop within a loop. An example of this would be where you want to print the names of the worksheets of each open workbook.

The first loop would go through each workbook. Each time this loop runs it would use a second loop to go through all the worksheets of that workbook. It is actually much easier to do than it sounds.

 
 
The following code shows how

Sub ListWorksheets()

    Dim i As Long, j As Long
    ' First Loop goes through all workbooks
    For i = 1 To Workbooks.Count

        ' Second loop goes through all the worksheets of workbook(i)
        For j = 1 To Workbooks(i).Worksheets.Count
            Debug.Print Workbooks(i).Name + ":" + Worksheets(j).Name
        Next j

    Next i

End Sub

 
 
This works as follows

 
The first loop sets i to 1

 
The second loop then uses the workbook at 1 to go through the worksheets.

 
The first loop sets i to 2

 
The second loop then uses the workbook at 2 to go through the worksheets.

 
and so on

 
 
It the next section we will use a For Each loop to perform the same task. You will find the For Each version much easier to read.

 
 

The VBA For Each Loop

The VBA For Each loop is used to read items from a collection or an array. We can use the For Each loop to access all the open workbooks. This is because Application.Workbooks is a collection of open workbooks.

 
 
This is a simple example of using the For Each Loop

    Dim wk As Workbook
    For Each wk In Workbooks
        Debug.Print wk.FullName
    Next wk

 
 

 Format of the For Each Loop

For Each <variable> in <collection>
Next <variable>

To create a For Each loop we need a variable of the same type that the collection holds. In the example here we created a variable of type Workbook.

If the collection has different types of items we can declare the variable as a variant.

VBA contains a collection called Sheets. This is a collection of sheets of type Worksheet(normal) and Chart(when you move a chart to be a full sheet). To go through this collection you would declare the variable as a Variant.

 
 
The following code uses For Each to print out the name of all the sheets in the current workbook

    Dim sh As Variant
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh

 
 

Order of Items

For Each goes through items in one way only.

For example, if you go through all the worksheets in a workbook it will always go through from left to right. If you go through a range it will start at the lowest cell e.g. Range(“A1:A10”) will return A1,A2,A3 etc.

This means if you want any other order then you need to use the For loop.

 
 
Both loops in the following example will read the worksheets from left to right.

    ' Both loops read the worksheets from left to right
    Dim wk As Worksheet
    For Each wk In ThisWorkbook.Worksheets
        Debug.Print wk.Name
    Next

    Dim i As Long
    For i = 1 To ThisWorkbook.Worksheets.Count
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next

 
 
As you can see the For Each loop is neater to write. However if you want to read the sheets in any other order e.g. right to left then you have to use the for loop.

    ' Reading the worksheets from right to left
    Dim i As Long
    For i = ThisWorkbook.Worksheets.Count To 1 Step -1
        Debug.Print ThisWorkbook.Worksheets(i).Name
    Next

 
 

 For Each With Arrays

One thing to keep in my is that the For Each loop is that it is read-only when you use it with arrays.

 
 
The following example demonstrates this

Sub UseForEach()

    ' Create array and add three values
    Dim arr() As Variant
    arr = Array("A", "B", "C")

    Dim s As Variant
    For Each s In arr
        ' Changes what s is referring to - not value of array item
        s = "Z"
    Next

    ' Print items to show the array has remained unchanged
    For Each s In arr
        Debug.Print s
    Next

End Sub

 
 
In the first loop we try to assign s to “Z”. When happens is that s is now referring the string “Z” and no longer to the item in the array.

In the second loop we print out the array and you can see that none of the values have changed.

 
 
When we use the For Loop we can change the array item. If we change the previous code to use the For Loop you it will change all the array values to “Z”

Sub UsingForWithArray()

    ' Create array and add three values
    Dim arr() As Variant
    arr = Array("A", "B", "C")

    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        ' Changes value at position to Z
        arr(i) = "Z"
    Next

    ' Print items to show the array values have change
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next

End Sub

 
 

If your Collection is storing Objects the you can change the items using a For Each loop.

 
 

Nested For Each Loop

We saw already that you can have a loop inside other loops. Here is the example from above

Sub ListWorksheets()

    Dim i As Long, j As Long
    ' First Loop goes through all workbooks
    For i = 1 To Workbooks.Count

        ' Second loop goes through all the worksheets of workbook(i)
        For j = 1 To Workbooks(i).Worksheets.Count
            Debug.Print Workbooks(i).Name + ":" + Worksheets(j).Name
        Next j

    Next i

End Sub

 
 

This time we will use the For Each loop to perform the same task

Sub ReadAllWorksheets()

    Dim wk As Workbook, sh As Worksheet
    ' Read each workbook
    For Each wk In Workbooks

        ' Read each worksheet in the wk workbook
        For Each sh In wk.Worksheets
            ' Print workbook name and worksheet name
            Debug.Print wk.Name + ": " + sh.Name
        Next sh

    Next wk

End Sub

 
 

As you can see this is a neater way of performing this task than using the For Loop.

This code run as follows:

  1. Get the first Workbook from the Workbooks collection
  2. Go through all the worksheets in this workbook
  3. Print the workbook/worksheet details
  4. Get the next workbooks in the collection
  5. Repeat steps 2 to 3
  6. Continue until no more workbooks are left in the collection

 
 

Summary

 
 

For

  • The For  loop is slower than the For  Each loop.
  • The For loop can go through a selection of items e.g. 5 to 10.
  • The For loop can read items in different orderse.g. 10 to 1.
  • The For loop is not as neat to write as the For Each Loop especially with nested loops.
  • To exit a For loop use Exit For.

 
 

For Each

  • The For Each loop is faster than the For loop.
  • The For Each loop goes through all items in the collection\array.
  • The For Each loop can go through items in one order only.
  • The For Each loop is neater to write than a For Loop especially for nested loops.
  • To exit a For Each loop use Exit For.

 
 

What’s Next?

Loops are used a lot with Arrays and Collections. It is important to understand them if you use VBA. You can read full explanations about them here: Arrays and Collections.

Loops are also used with Workbooks, Worksheets and Ranges. Click on the links to check out an in-depth post on each topic.

You can also view a list of all the VBA posts on the site here.

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook

 
 

12 comments

  1. Paul:

    In Sub ListWorksheets() you must change Worksheets(i) to Worksheets(j). Also I was getting “error 9” at execution. I changed

    Debug.Print Workbooks(i).Name + “:” + Worksheets(j).Name

    to

    Debug.Print Workbooks(i).Name + “:” + Workbooks(i).Worksheets(j).Name

    and it worked perfectly.

    Sincerely yours,

    K-Li-Ch

  2. Private Sub CommandButton3_Click()

    ‘ Get the last row with text
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Dim i As Long, Total As Long

    ‘ Use LastRow in loop
    For i = 1 To LastRow

    ‘ Check if cell has text “Oranges1”
    If Cells(i, 1).Value = “Oranges1” Then

    ‘ Add value in column B to total
    Total = Sheets(1).Cells(i, 2).Value + Sheets(2).Cells(i, 2).Value

    Exit For
    End If
    Next i

    ‘ Print total
    MsgBox “Total oranges sold was = ” & Total

    End Sub

    ////////////////////////

    Hi Paul!
    I’m having a situation where I want to use the total gain from this looping to another excel workbook and at the same time I don’t want to use the msgbox but just want the result and put it on another workbook. Is there’s any possibilities that I can do that? What should I add to the code? I have tried some of this;

    Set Target_Workbook = Workbooks.Open(Target_Path)
    Set Source_Workbook = ThisWorkbook

    and using this

    Source_Workbook.Sheets(1).Cells(19, 4) = Target_Data

    but it seems not to work out well. Can you help me with this?

    1. You are assigning the value to the source workbook. Should this not be to the Target_Workbook? e.g.

      Target_Workbook.Sheets(1).Cells(19, 4) = Target_Data

  3. I have 2 worksheets with one single column common in both which is the 1st column of both worksheets. I want to match the columns in both sheets and for matching columns ( i.e. for Sheet1.Ax = Sheet2.Ax) I want all rows in that row from Sheet 2 to the last row column of the sheet 1 . I am using below code –
    Dim LastCol As Long
    Dim rng As Range
    Dim rng2 As Range
    Dim i As Integer

    ‘ Use all cells on the sheet
    Set rng = Sheets(“Sheet1”).Cells
    Set rng2 = Sheets(“Sheet2”).Cells

    ‘ Find the last column
    LastCol = Last(2, rng)

    ‘ After the last column with data change the value of the cell in row 1

    For i = 1 To 895
    rng.Parent.Cells(i, LastCol + 1).Value = rng2.Parent.Cells(i,1j).Value
    Next I

    With above coe I am able to only copy the first column from Sheet2 where as I need to copy all columns for sheet2 is the 1st column in sheet2 is same as 1st column in Sheet1
    Can you please help with the actual code

Leave a Reply

Your email address will not be published. Required fields are marked *