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.

Going to the developer option to select Visual basic for creating Macro

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

How to create module


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 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:

Code with Nested For Loop for Creating Multiplication Table)

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

Code Breakdown

For r = 1 To 10: This iterates from r = 1 to  r= 10.

For c = 1 To 10: This iterates from c = 1 to c = 10 for each value of r.

Cells(r + 3, c + 1).Value = r * c:  This line put the product of r and c in a cell that has row number r+3 (4th row for r=1) and column number c+1 (2nd column, B for c= 1). Hence the resulting output will be shown from cell B4 and continue until K13.

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.

Created Multiplication Table After Executing Code)


Example 2: Using Nested For Loops to Find Duplicates

Result After Executing the Code to Get Duplicate Values)

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:

Code with Nested For Loop for Checking Duplicate Values)

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

How Does the Code Work?

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

Here, we have assigned names to the lists (list1,list2 & output)

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

The for loop will iterate from 1 to the total number of elements in list1. Then it will take the element of list1 as common.

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

Here, another For loop will iterate for j=1 to the total number of elements in list2. Within this loop, an If statement will check if the jth element of list2 is the same as common (ith element of list1). If the condition is true, then the number of common elements will be increased by 1 and the common element will be placed on the output range. Then the loop will be continued.

  • Running the code will populate the output range with the common names (duplicates).
Result After Executing the Code to Get Duplicate Values)

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.

Code with Nested For Loop for Sorting Numbers in Ascending Order)

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

Code Breakdown

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

This will prompt the user to select the range of numbers to sort.

numbers = Input_Range.Value

This will read the numbers from the input range into an array named numbers.

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

In this part of the code, the 1st For loop will iterate from the first element to the element before the last element of the list. Then it will check whether this element is greater than any elements after its position in the list. If the code finds any element greater than its following element, then they will swap positions. In this way, it will sort all the numbers in ascending order. Input_Range.Value = numbers.

This piece of code will rewrite the sorted numbers back into the list.

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.

Code with Nested Do While Loop for Checking Duplicate Values)

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

Code Breakdown

This code works in the same ways as in the case of Nested For loop. The only difference is that we have used the Do While loop twice instead of For loop. Here, the outer Do While loop iterates through each element in list_1 and matches with all the elements in list_2 with the help of the inner Do While loop. If it finds a match (Match_found= True), the code will assign the match value to common_term and show it in column E.

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

Result After Executing the Code to Get Duplicate Values)


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.

Code with Nested Do Until Loop for Checking Duplicate Values)

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.

Result After Executing the Code to Get Duplicate Values)


Method 3 – Do Loop Until for Finding Duplicates:

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

Code with Nested Loop Until for Checking Duplicate Values)

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.

Result After Executing the Code to Get Duplicate Values)


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.

VBA Code consisting Exit For Command)

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

Code Breakdown

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

Here, two For loops are working. The outer For loop iterates from i = 1 to 5 and the inner For loop iterates from j= 1 to 5.

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

In the inner For loop, there is an If statement where it checks whether the product of i and j is greater than 25. If the condition is true then the inner For loop will be immediately terminated using the Exit For command. Otherwise, it will print i, j, and i*j in the immediate window. Then it will loop again.

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

In the outer loop, there is another If statement where the code will again check whether i*j is greater than 25. If the condition is true, the outer loop will be terminated as well using the  Exit For command. If the condition is false then the outer loop will continue.

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

Result of Using Exit For loop)

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

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo