Excel VBA: Introduction to the FOR (Next) Loop

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video.

This is a beginner’s level video, but by the end of this session, I am positive that you will be able to perform complex operations over a large dataset with confidence. We will be covering 6 practical use case scenarios and this session is loaded with Tips and Tricks that I hope will enrich your VBA programming skillset.

6 Practical Examples of FOR Loop

This is a dataset of Paid invoices.

We will be populating Column E with static values (Paid).

2. In Column F, we will apply a formula where we multiply Col C with a percentage.

3. In Column G, we will use conditional logic. If amount in Col C, is greater than 10000 we will add the value “Too High”. If its less than or equal to 10000, we will add the value “Normal”.

4. In Column H, we will lookup the Region that corresponds to the Store Name based on this mapping sheet. We will do this by calling the traditional Vlookup function through VBA.

5. In Column I, we will perform the same lookup. But, this time we will achieve it programmatically by looping over the main dataset and the mapping dataset. This is a Nested For Loop.

6. And lastly, we can see some Null values in the Date column. We will delete the off the entire row if the Date value is Null.

What is a For Loop?

A FOR loop allows you to perform the SAME operation for a SET number of times.

In this session, we are going to cover a specific type of For Loop: The For Next Loop, which is super-effective in looping over datasets.

The basic logic for the For Loop that we will follow in this course is given here:

For Counter = Start To End
	Perform An Operation
Next Counter

Let’s break this down first.

Line 1 states the number of times we want to perform the operation, or more technically, it tells us the number of iterations we want to run within the loop. We will need to provide the start number and the end number.

Line 2 is the operation itself.

And after we’ve performed the operation, Line 3 allows us to move to the next iteration of the loop, so that we can perform the operation again. And this will continue till we reach the End number i.e. the end of the loop.

And lastly, we have this term Counter. I call this the iteration variable. Once the loop starts, at any point within the loop the Counter will tells us which iteration of the loop we are currently in.

Suppose, we are running a loop from 1 to 3.

I,e For Counter = 1 to 3.

In the first iteration of the loop, the Counter will be 1, in the second iteration of the loop the Counter will be 2 and in the third and final iteration of the loop the Counter will be 3.

Microsoft Theory

Challenge 1: Basic FOR Loop

The objective of this module is to populate the text value Paid in each cell in Column E, from the start to the end of the dataset.

Sub For_Loop_First_Macro()

    ThisWorkbook.Sheets("Invoice").Columns("E").Clear
    ThisWorkbook.Sheets("Invoice").Range("E1").Value = "Status"
    Dim lrow As Long
    lrow = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count
    
    Dim i As Long
    For i = 2 To lrow
        ThisWorkbook.Sheets("Invoice").Range("E" & i).Value = "Paid"
    Next i

End Sub

Notes:

To programmatically figure out the last row of the dataset, we can determine the Current Region of the dataset, and simply count the number of rows in it which will give us the last row.

    Dim lrow As Long
    lrow = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count

Dynamically reference the current cell in Col E.

Range("E" & i).Value

Challenge 2: Perform Calculations

In column F, we will fill in the Gross Amount which is the Amount in Column C plus a 15% tax. The formula in the first cell will be:

=C2*1.15

Full Code:

Sub For_Loop_Second_Macro()

    ThisWorkbook.Sheets("Invoice").Columns("F").Clear
    ThisWorkbook.Sheets("Invoice").Range("F1").Value = "Gross Amount"
    Dim lrow As Long
    lrow = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count
    
    Dim i As Long
    For i = 2 To lrow
        ThisWorkbook.Sheets("Invoice").Range("F" & i).Value = ThisWorkbook.Sheets("Invoice").Range("C" & i).Value * 1.15
    Next i

End Sub

Challenge 3: Use IF Statements

In Column G, we want to add some conditional logic. If the amount in column C is greater than 10000, then populate the value “Too High”. If its less than or equal to 10000, we will populate “Normal”.

The structure of the IF Statement will be:

If (ThisWorkbook.Sheets("Invoice").Range("C" & i).Value > 10000) Then
            'Do Something
            Else
            'Do Something Else
End If

Full Code:

Sub For_Loop_Third_Macro()

    ThisWorkbook.Sheets("Invoice").Columns("G").Clear
    ThisWorkbook.Sheets("Invoice").Range("G1").Value = "Check"
    Dim lrow As Long
    lrow = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count
    
    Dim i As Long
    For i = 2 To lrow
        If (ThisWorkbook.Sheets("Invoice").Range("C" & i).Value > 10000) Then
            ThisWorkbook.Sheets("Invoice").Range("G" & i).Value = "Too High"
            Else
            ThisWorkbook.Sheets("Invoice").Range("G" & i).Value = "Normal"
        End If
        
    Next i

End Sub

Challenge 4: Call Excel Functions

For this exercise, we are going to perform a vlookup in Col H, where we will lookup the Store Name from Col D within the dataset in the Mapping sheet, and grab the corresponding Region Name that the Store belongs to.

Performing the vlookup manually in the first Cell H2 would look like:

=VLOOKUP(D2,Mapping!A:B,2,FALSE)

Mapping Dataset:

Microsoft Info on using Application.WorksheetFunction

Full Code

Sub For_Loop_Fourth_Macro()

    ThisWorkbook.Sheets("Invoice").Columns("H").Clear
    ThisWorkbook.Sheets("Invoice").Range("H1").Value = "Manual Region Lookup"
    Dim lrow As Long
    lrow = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count
    
    Dim i As Long
    For i = 2 To lrow
        On Error Resume Next
        ThisWorkbook.Sheets("Invoice").Range("H" & i).Value = _
                            Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets("Invoice").Range("D" & i), _
                            ThisWorkbook.Sheets("Mapping").Columns("A:B"), 2, False)
        On Error GoTo 0
    Next i

End Sub

Challenge 5: Nested FOR Loops

We want to perform a lookup in Column I for the same Store Name value. But, this time, for each iteration of the loop, we will grab the Store Name, and then loop over the Mapping dataset, search for the store name in column A, and once a Match is found, we will return the corresponding Region Value from column B.

So, we are going to perform two loops. One main loop. And a second loop within each iteration of the main loop. This is called a Nested For Loop.

Tip: Comment out the Outer Loop, and build out the Inner Loop with a hardcoded reference to the first cell of the Outer Loop. Test is Out. If it works, remove the comments of the Outer Loop. If you are still uncertain, step through the code line by line using the F8 key and check out what values are being populated within each loop.

Full Code:

Sub For_Loop_Fifth_Macro()

    ThisWorkbook.Sheets("Invoice").Columns("I").Clear
    ThisWorkbook.Sheets("Invoice").Range("I1").Value = "Auto Region Lookup"
    Dim lrowInv As Long
    lrowInv = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count
    Dim lrowMap As Long
    lrowMap = ThisWorkbook.Sheets("Mapping").Range("A1").CurrentRegion.Rows.Count
    
    Dim i As Long
    Dim j As Long
    Dim sLookupValue As String, sLookupMatch As String, sLookupReturn As String

    For i = 2 To lrowInv
        sLookupValue = ThisWorkbook.Sheets("Invoice").Range("D" & i).Value
        For j = 2 To lrowMap
            sLookupMatch = ThisWorkbook.Sheets("Mapping").Range("A" & j).Value
            sLookupReturn = ThisWorkbook.Sheets("Mapping").Range("B" & j).Value
            If sLookupValue = sLookupMatch Then
                ThisWorkbook.Sheets("Invoice").Range("I" & i).Value = sLookupReturn
                Exit For
            End If
        Next j
    Next i

End Sub

Challenge 6: Reverse FOR Loop

Have a look at Col A in our dataset. We can see some NULL values. So, for our final challenge, we would like to delete the Entire row wherever there is a NULL value in column A. Our answer will involve a Reverse For Loop i.e. we will be looping from bottom to top of the dataset this time.

Why do we do a Reverse Loop?

We can check out our dataset. We have a Null value in Column A Row 8. Try deleting it manually. Observe what happen. The bottom line moves one up. So, the previous row 9 is now the new row 8. But, if we are within a For Loop, the Loop will move to the new row 9 which was previously row 10. So, we will land up skipping a line. To avoid this, we need to Loop from bottom to the top.

Note: We need to go from Last Row to the Second Row within the For Loop (Reverse Order) and explicitly tell Excel to decrement by 1 (Step -1)

Full Code

Sub For_Loop_Sixth_Macro()
    
    Dim lrow As Long
    lrow = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count
    
    Dim i As Long
    For i = lrow To 2 Step -1
        If ThisWorkbook.Sheets("Invoice").Range("A" & i).Value = "NULL" Then
            ThisWorkbook.Sheets("Invoice").Rows(i).Delete
        End If
    Next i

End Sub

FULL Consolidated Code

We can perform the first 5 challenges within a single macro. This will save us having to loop over the dataset 5 separate times.

Note: If you wanted to include the Delete condition as well, we would need to perform the entire For Loop in Reverse Order (i.e. the Lookups, Formulas etc. in reverse too).

Sub For_Loop_Mega_Macro()

'Clear data from previous macro run
ThisWorkbook.Sheets("Invoice").Columns("E:I").Clear
'Add column headers
    ThisWorkbook.Sheets("Invoice").Range("E1").Value = "Status"
    ThisWorkbook.Sheets("Invoice").Range("F1").Value = "Gross Amount"
    ThisWorkbook.Sheets("Invoice").Range("G1").Value = "Check"
    ThisWorkbook.Sheets("Invoice").Range("H1").Value = "Manual Region Lookup"
    ThisWorkbook.Sheets("Invoice").Range("I1").Value = "Auto Region Lookup"
'Find last row of Invoice dataset
    Dim lrowInv As Long
    lrowInv = ThisWorkbook.Sheets("Invoice").Range("A1").CurrentRegion.Rows.Count
'Find last row of Mapping dataset
    Dim lrowMap As Long
    lrowMap = ThisWorkbook.Sheets("Mapping").Range("A1").CurrentRegion.Rows.Count
'Declare the variables that we will be using in the For loop
    Dim i As Long
    Dim j As Long
    Dim sLookupValue As String, sLookupMatch As String, sLookupReturn As String
' Start the For Loop
    For i = 2 To lrowInv
        'Challenge 1: Populate cells with Paid value
        ThisWorkbook.Sheets("Invoice").Range("E" & i).Value = "Paid"
        'Challenge 2: Populate cells with output of Formula
        ThisWorkbook.Sheets("Invoice").Range("F" & i).Value = ThisWorkbook.Sheets("Invoice").Range("C" & i).Value * 1.15
        'Challenge 3: Populate cells with output of If condition
        If (ThisWorkbook.Sheets("Invoice").Range("C" & i).Value > 10000) Then
            ThisWorkbook.Sheets("Invoice").Range("G" & i).Value = "Too High"
            Else
            ThisWorkbook.Sheets("Invoice").Range("G" & i).Value = "Normal"
        End If
        'Challenge 4: Do a manual vlookup
        On Error Resume Next
        ThisWorkbook.Sheets("Invoice").Range("H" & i).Value = _
                            Application.WorksheetFunction.VLookup(ThisWorkbook.Sheets("Invoice").Range("D" & i), _
                            ThisWorkbook.Sheets("Mapping").Columns("A:B"), 2, False)
        On Error GoTo 0
        'Challenge 5: Do a lookup using Nested For Loop
        sLookupValue = ThisWorkbook.Sheets("Invoice").Range("D" & i).Value
        For j = 2 To lrowMap
            sLookupMatch = ThisWorkbook.Sheets("Mapping").Range("A" & j).Value
            sLookupReturn = ThisWorkbook.Sheets("Mapping").Range("B" & j).Value
            If sLookupValue = sLookupMatch Then
                ThisWorkbook.Sheets("Invoice").Range("I" & i).Value = sLookupReturn
                Exit For
            End If
        Next j
    Next i
    
End Sub

6 Practical Examples of FOR Loop

What is a For Loop?

Challenge 1: Basic FOR Loop

Challenge 2: Perform Calculations

Challenge 3: Use IF Statements

Challenge 4: Call Excel Functions

Challenge 5: Nested FOR Loops

Challenge 6: Reverse FOR Loop

Related Posts

Calling Subs and Functions

Lock and Unlock Files and Sheets

Crash Course Excel VBA | 15 Min Tutorial