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

1. 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
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``````