How to Use Excel VBA Nested For Loop (3 Examples)

How to Launch the VBA Editor in Excel

• First, ensure that the Developer tab is displayed on your ribbon. If it’s not visible, learn how to enable the Developer tab.
• Go to the Developer tab.
• Select Visual Basic.

• A new window will open. Follow these steps:
  • Click Insert.
  • Choose Module.
  • A new module will be created.

Creating a VBA Nested For Loop in Excel

A nested For Loop is essentially a For loop within another For loop. Here’s an example of what a Nested Loop looks like:

Sub Nested_For()
For i = 1 To 5
For j = 1 To 10
'Code to be executed
Next j
Next i
End Sub

In this code:

• i is the counter variable for the outer (1st) For loop.
• j is the counter variable for the inner (2nd) For loop.
• For each value of i, the code will run 10 times as j iterates from 1 to 10. So the total number of executions is 5 * 10 = 50 times.

You can nest as many For loops as needed, but keep in mind that the more loops are nested, the harder it becomes to track the code. It’s recommended to use no more than 3 loops in a nested structure.

Read More: Excel VBA to Use For Loop with Two Variables

Example 1 – Creating a Multiplication Table Using Nested For Loops

In this example, we’ll write a VBA code using nested For loops to create a multiplication table in an Excel worksheet. The result will resemble the illustration above.

To do that, we have used the following code:

Sub Nested_Forloop_MultiplicationTable()
For r = 1 To 10
For c = 1 To 10
Cells(r + 3, c + 1).Value = r * c
Next c
Next r
End Sub

Run the code by clicking F5 or opening the Macro window from the Developer tab. You’ll see the resulting multiplication table in your worksheet.

Example 2 – Using Nested For Loops to Find Duplicates

We can employ nested For loops to identify common elements (duplicates) between two lists, as illustrated above. Let’s consider two lists containing fruit names. Our goal is to find duplicate names in column E. To achieve this using VBA code with a nested For loop, follow the steps below:

Sub Find_Common_Terms()
Dim list1 As Range
Dim list2 As Range
Dim common As String
Dim output As Range
Dim i As Long
Dim j As Long
Dim k As Long
Dim number As Integer
number = 0'Set the ranges for the two lists and the output column
Set list1 = Range("B5:B13")
Set list2 = Range("C5:C13")
Set output = Range("E5")' #Loop1: Loop through each item in list1
For i = 1 To list1.Count
'Get the value of the current item in list1
common = list1(i).Value'#Loop2: Loop through each item in list2
For j = 1 To list2.Count
'Check if the current item in list2 matches the current item in list1
If common = list2(j).Value Then
number = number + 1
output.Cells(number, 1).Value = common
End If
Next j
Next i
End Sub

Set list1 = Range("B5:B13")
Set list2 = Range("C5:C13")
Set output = Range("

For i = 1 To list1.Count
common = list1(i).Value

For j = 1 To list2.Count
            If common = list2(j).Value Then
                number = number + 1
                output.Cells(number, 1).Value = common
            End If
        Next j

• Running the code will populate the output range with the common names (duplicates).

Example 3 – Sorting Data Using Nested For Loops

• We want to sort a list of numbers in ascending order using a VBA Nested For loop.

Sub Sort_Numbers()
    Dim Input_Range As Range
    Dim numbers() As Variant
    Dim i As Long
    Dim j As Long
    Dim tempo As Variant
    'Get the range of numbers to sort from an input box
    Set Input_Range = Application.InputBox("Enter a range of numbers to sort:", Type:=8)
    'Read the numbers from the input range into an array
    numbers = Input_Range.Value
    '#Loop1: Loop through each element in the array
    For i = LBound(numbers, 1) To UBound(numbers, 1) - 1
    '#Loop2: Loop through each element from next to i th element to the last element in the array
        For j = i + 1 To UBound(numbers, 1)
            'Compare the current element with the next element and swap if necessary
            If numbers(i, 1) > numbers(j, 1) Then
                tempo = numbers(i, 1)
                numbers(i, 1) = numbers(j, 1)
                numbers(j, 1) = tempo
            End If
        Next j
    Next i

Set Input_Range = Application.InputBox("Enter a range of numbers to sort:", Type:=8)

numbers = Input_Range.Value

For i = LBound(numbers, 1) To UBound(numbers, 1) - 1
For j = i + 1 To UBound(numbers, 1)
If numbers(i, 1) > numbers(j, 1) Then
tempo = numbers(i, 1)
numbers(i, 1) = numbers(j, 1)
numbers(j, 1) = tempo
End If
Next j
Next i

Now, as we want to run the code multiple times, it is easier to run the code by creating a button on the worksheet. Hence, we have added a button and assigned the macro to it. Now if you click on the button, the macro will run. Then, you just need to select the range of numbers and it will automatically sort the numbers for you. The output is below:

Read More: Excel VBA: For Loop Skip to Next Iteration

Some Alternative Methods to Use Excel VBA Nested For Loop

Apart from the For loop, VBA has some alternatives that we can use to accomplish similar types of tasks. We’ll discuss some of these below:

Method 1 – Nested Do While Loop to Get Duplicates in Excel VBA

Here, I have taken the same dataset from example 2 to find the common terms from both lists using the Nested Do While Loop. Here, I have used the following code.

Sub FindCommonTermsDoWhile()
Dim list_1 As Range, list_2 As Range, output_rng As Range
Dim i As Integer, j As Integer, k As Integer
Dim common_term_term As String, Match_Found As Boolean
' Define the ranges for list_1, list_2, and the output_rng
Set list_1 = Range("B5:B13")
Set list_2 = Range("C5:C13")
Set output_rng = Range("E5")
' Loop through each cell in list_1
i = 1
Do While Not IsEmpty(list_1(i))
' Reset the Match_Found variable _
to false for each iteration of the outer loop
Match_Found = False
' Loop through each cell in list_2
j = 1
Do While Not IsEmpty(list_2(j))
' Check if the value in list_1 matches the value in list_2
If list_1(i).Value = list_2(j).Value Then
' Set the Match_Found variable to true and save the common_term
Match_Found = True
common_term = list_1(i).Value
' Exit the inner loop once a match is found
Exit Do
End If
' Move to the next cell in list_2
j = j + 1
Loop
' If a match was found, add the common_term term to the output range
If Match_Found Then
output_rng.Offset(k, 0).Value = common_term
k = k + 1
End If
' Move to the next cell in list_1
i = i + 1
Loop
End Sub

After we run the code, the result will be similar to what we have seen in example 2.

Method 2 – Nested Do Until Loop to Get Duplicates

Now, we will use Nested Do Until Loop to find the common terms from both lists that we have shown in the previous section. The code that I have used here is given below.

Sub FindCommonTerms_DoUntil()
Dim list_1 As Range, list_2 As Range, output_rng As Range
Dim i As Integer, j As Integer, k As Integer
Dim common_term_term As String, Match_Found As Boolean
' Define the ranges for list_1, list_2, and the output_rng
Set list_1 = Range("B5:B13")
Set list_2 = Range("C5:C13")
Set output_rng = Range("E5")
' Loop through each cell in list_1
i = 1
Do Until IsEmpty(list_1(i))
' Reset the Match_Found variable _
to false for each iteration of the outer loop
Match_Found = False
' Loop through each cell in list_2
j = 1
Do Until IsEmpty(list_2(j))
' Check if the value in list_1 matches the value in list_2
If list_1(i).Value = list_2(j).Value Then
' Set the Match_Found variable to true and save the common_term
Match_Found = True
common_term = list_1(i).Value
' Exit the inner loop once a match is found
Exit Do
End If
' Move to the next cell in list_2
j = j + 1
Loop
' If a match was found, add the common_term term to the output range
If Match_Found Then
output_rng.Offset(k, 0).Value = common_term
k = k + 1
End If
' Move to the next cell in list_1
i = i + 1
Loop
End Sub

The structure is similar, but we replace Do While Not with Do Until. Running that code will also yield duplicate values in column E.

Method 3 – Do Loop Until for Finding Duplicates

Here, I have done the same task using Nested Loop Until. The code is given below.

Sub FindCommonTerms_LoopUntil()
Dim list_1 As Range, list_2 As Range, output_rng As Range
Dim i As Integer, j As Integer, k As Integer
Dim common_term_term As String, Match_Found As Boolean
' Define the ranges for list_1, list_2, and the output_rng
Set list_1 = Range("B5:B13")
Set list_2 = Range("C5:C13")
Set output_rng = Range("E5")
' Loop through each cell in list_1
i = 1
Do
' Reset the Match_Found variable _
to false for each iteration of the outer loop
Match_Found = False
' Loop through each cell in list_2
j = 1
Do
' Check if the value in list_1 matches the value in list_2
If list_1(i).Value = list_2(j).Value Then
' Set the Match_Found variable to true and save the common_term
Match_Found = True
common_term = list_1(i).Value
' Exit the inner loop once a match is found
Exit Do
End If
' Move to the next cell in list_2
j = j + 1
Loop Until IsEmpty(list_2(j))
' If a match was found, add the common_term term to the output range
If Match_Found Then
output_rng.Offset(k, 0).Value = common_term
k = k + 1
End If
' Move to the next cell in list_1
i = i + 1
Loop Until IsEmpty(list_1(i))
End Sub

In this method, the code structure is similar to the previous examples, but we use a Loop Until construct. Running this code will yield duplicate values in column E.

How to Break a Excel VBA Nested For Loop in Excel VBA

Breaking a nested For loop, you can use the Exit For/Do command.

Below is an example given where we have used the Exit For command for immediately exiting a For loop based on a condition.

Sub Exit_For_Example()
Dim i As Integer
Dim j As Integer
For i = 1 To 5
For j = 1 To 5
If i * j > 25 Then
Exit For
End If
Debug.Print i, j, i * j
Next j
If i * j > 25 Then
Exit For
End If
Next i
End Sub

For i = 1 To 5
For j = 1 To 5

If i * j > 25 Then
Exit For
End If
Debug.Print i, j, i * j
Next j

If i * j > 25 Then
Exit For
End If
Next i

After running the code, we will have the following output.

Read More: Excel VBA: How to Exit a For Loop

Frequently Asked Questions (FAQs)

1. What is Nested For Loop in VBA?

A nested For loop consists of one For loop inside another For loop. You achieve this by placing a For……Next statement within another For…….Next statement.

2. What is the Purpose of Using Nested For loops in VBA?

• Nested For loops are commonly used for iterating through multidimensional data, such as 2D arrays or tables with multiple columns and rows.

3. How do you write a Nested For loop in VBA?

To create a nested For loop, simply include another For……Next statement inside an existing one. The inner loop runs completely for each iteration of the outer loop.

4. What are the Common Mistakes to avoid when using Nested For loops in VBA?

Some common mistakes to avoid when working with nested For loop may include

• Using incorrect variable names.
• Forgetting to include the Next statement to complete the loop.
• Overusing nested loops.

Be vigilant about these issues to ensure your code executes correctly.

5. Can you use exit statements inside Nested For loops in VBA?

Yes, you can use the Exit For command to prematurely exit For loop.

Things to Remember

• Limit the number of nested loops (ideally not more than 3) to maintain code readability.
• Choose descriptive variable names for iteration variables to minimize confusion.
• Always include the Next command to properly close the loop.
• Use the Exit For command hen needed to exit a loop early.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Continue Excel VBA For Loop
• Excel VBA For Loop with Array
• Excel VBA: Running a Backwards For Loop
• Excel VBA with For Loop Decrement