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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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 module


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.

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.

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.

Created Multiplication Table After Executing Code)


2. Utilizing Nested For Loop to Get Duplicates

Result After Executing the Code to Get Duplicate Values)

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.

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.

  • Now if you run the code, you will get the common names.
Result After Executing the Code to Get Duplicate Values)

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.

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

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)


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

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.

Result After Executing the Code to Get Duplicate Values)


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

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.

Result After Executing the Code to Get Duplicate Values)


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.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

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