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

In this article, I am going to introduce you to one of the most used tools in Excel VBA and that is Nested For Loop. If you already know the uses of For loops, the concept of Excel VBA Nested For loop will be very easy to understand. So let’s get started.

How to Launch VBA Editor in Excel

In this section, we are going to demonstrate how to launch a VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.

• First, we go to the Developer tab.
• Then we will select Visual Basic.

Then a new window will pop up. Then we are going to follow these steps,

• First, we will select Insert,
• Then we are going to select Module.
• A new Module will be created.

How to Create a VBA Nested For Loop in Excel

A nested For Loop is nothing but For loop within another For loop. Here is what a sample 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

Here, i is the counter variable for the 1st For loop and j is the counter variable for the 2nd For loop. Now for each value of i, the code will run 10 times as j will iterate from 1 to 10. So, the total number of times the code will be executed is 5*10 or  50 times.

In the above code, two For loops are nested, but we can nest as many For loops as we want. But you have to keep in mind that the more loops are nested together, the harder it will be to keep track of the code. So it is recommended to use not more than 3 loops in a nested loop.

More details on the usage of this code is in the following section.

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

Excel VBA Nested For Loop: 3 Useful Examples

In this section, we will demonstrate 3 practical examples of using Excel VBA Nested For loop with appropriate illustrations.

1. Creating Multiplication Table Using Nested For Loop

In the example below, we will write a VBA code using the Nested For loop to create a multiplication table in an Excel worksheet. The result will look like the above illustration.

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

For r = 1 To 10

Cells(r + 3, c + 1).Value = r * c

Now, run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, you will see the following result.

2. Utilizing Nested For Loop to Get Duplicates

We can also use the Nested For loop for checking common elements(duplicates) within two lists as shown in the above illustrations. To show this, I have taken two lists containing some names of fruits. Now, I want to find out the duplicate names in column E. To do that using VBA code with Nested For loop, I have used the following code.

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

• Now if you run the code, you will get the common names.

3. Sorting Data Using Nested For Loop

We can also use the Nested For loop to sort a list of numbers in ascending order. For illustration, I have taken a dataset where we have two lists of some numbers. Now to sort the two lists using VBA Nested For loop, I have used the following code.

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. Here you can see the output:

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. Below, some of them are discussed.

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.

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

Here, the whole structure of the code is quite the same as the one where we have used the Nested Do While loop. But, here we replace the Do While Not with Do Until where both give the same result.

• Now, if we run the code, we will get the duplicate values in column E.

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

Here, the code works in a similar fashion as we have seen in the previous two sections. The only difference is that the bloc of code inside Do will be executed first, then the condition will be checked later at Loop Until.

• Now, if we run the code, we will get the duplicate values in column E.

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

If we need to, we can immediately exit a loop using the Exit For/Do command.

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

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 is basically a For loop inside another For loop. This is used by writing For……Next statement inside another For…….Next statement.

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

A nested For loop is usually used for iterating through multidimensional data for example 2D array or a table with multiple columns and rows.

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

For writing Nested For loop, just put another For……Next statement inside a For…..Next statement. Here, the inner loop will run 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 while creating and executing a nested For loop may include

• Using wrong variable names.
• Forgetting to write Next to complete the loop.
• Using too many Nested loops.

While the mistakes are not limited to these, you should always watch out for them. Otherwise, the code won’t execute.

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

• As the use of multiple For loops inside nested loops makes a code difficult to understand and debug, keep the number of loops as low as possible (not more than 3)
• To minimize confusion, use descriptive variable names for iteration variables.
• Don’t forget to write the Next command to complete the loop.
• Use the Exit For Command to prematurely exit any loop.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

That is the end of this article regarding how to create and use Excel VBA Nested For loop. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

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