Nested Loops in VBA

A nested loop is a loop inside another loop.

For example:

For i = 1 To 3

    For j = 1 To 3

        MsgBox i & " - " & j

    Next j

Next i

Here, the For j loop is inside the For i loop.

Nested loops are useful when a task has more than one level of repetition.

• Processing rows and columns.
• Creating tables.
• Generating multiplication tables.
• Processing student marks.
• Comparing multiple records.
• Formatting a group of cells.
• Working with multiple worksheets.

A simple nested For loop looks like this:

For i = 1 To 3

    For j = 1 To 3

        ' Inner loop statements

    Next j

Next i

The inner loop runs completely for every value of the outer loop.

The following example displays combinations of two numbers.

Dim i As Integer
Dim j As Integer

For i = 1 To 3

    For j = 1 To 3

        MsgBox i & " - " & j

    Next j

Next i

For every value of i, the inner loop runs from 1 to 3.

The outer loop is the loop that contains another loop.

For i = 1 To 3

    ' Outer loop

Next i

In a nested loop, the outer loop controls the larger repetition.

The inner loop is the loop placed inside the outer loop.

For i = 1 To 3

    For j = 1 To 5

        MsgBox j

    Next j

Next i

The inner loop runs five times for every iteration of the outer loop.

Suppose both loops run three times:

For i = 1 To 3

    For j = 1 To 3

        MsgBox i & " - " & j

    Next j

Next i

The inner loop completes all three iterations before the outer loop moves to its next value.

The combinations are:

• 1 - 1
• 1 - 2
• 1 - 3
• 2 - 1
• 2 - 2
• 2 - 3
• 3 - 1
• 3 - 2
• 3 - 3

Nested loops are very useful for processing rows and columns in Excel.

Dim i As Integer
Dim j As Integer

For i = 1 To 5

    For j = 1 To 3

        Cells(i, j).Value = i + j

    Next j

Next i

This processes five rows and three columns.

A nested loop can fill an Excel table automatically.

Dim rowNumber As Integer
Dim columnNumber As Integer

For rowNumber = 1 To 5

    For columnNumber = 1 To 5

        Cells(rowNumber, columnNumber).Value = "Data"

    Next columnNumber

Next rowNumber

The word Data is placed into a 5 × 5 area.

You can perform calculations inside nested loops.

Dim i As Integer
Dim j As Integer

For i = 1 To 5

    For j = 1 To 5

        Cells(i, j).Value = i * j

    Next j

Next i

This creates multiplication values in the worksheet.

Nested loops are excellent for creating multiplication tables.

Dim i As Integer
Dim j As Integer

For i = 1 To 10

    For j = 1 To 10

        Cells(i, j).Value = i * j

    Next j

Next i

The worksheet receives a 10 × 10 multiplication table.

An If statement can be used inside the inner loop.

Dim i As Integer
Dim j As Integer

For i = 1 To 5

    For j = 1 To 5

        If Cells(i, j).Value = "" Then

            Cells(i, j).Value = 0

        End If

    Next j

Next i

Empty cells in the selected area are filled with zero.

Nested loops can apply formatting to multiple rows and columns.

Dim i As Integer
Dim j As Integer

For i = 1 To 5

    For j = 1 To 4

        Cells(i, j).Font.Bold = True

    Next j

Next i

The selected 5 × 4 area is made bold.

The row and column counters can be used directly with the Cells property.

Dim rowNumber As Integer
Dim columnNumber As Integer

For rowNumber = 1 To 5

    For columnNumber = 1 To 4

        Cells(rowNumber, columnNumber).Value = _
        "R" & rowNumber & "C" & columnNumber

    Next columnNumber

Next rowNumber

Each cell receives its row and column position.

Nested loops can process marks for multiple students and multiple subjects.

Dim student As Integer
Dim subject As Integer

For student = 2 To 11

    For subject = 2 To 6

        If Cells(student, subject).Value >= 40 Then
            Cells(student, subject).Interior.ColorIndex = 4
        End If

    Next subject

Next student

The outer loop processes students and the inner loop processes subjects.

Nested loops can be used when each student has several pieces of information to process.

Dim studentRow As Integer
Dim columnNumber As Integer

For studentRow = 2 To 10

    For columnNumber = 1 To 4

        If Cells(studentRow, columnNumber).Value = "" Then

            Cells(studentRow, columnNumber).Value = "N/A"

        End If

    Next columnNumber

Next studentRow

Nested loops can also use For Each.

Dim ws As Worksheet
Dim cell As Range

For Each ws In ThisWorkbook.Worksheets

    For Each cell In ws.Range("A1:C5")

        cell.Font.Bold = True

    Next cell

Next ws

The outer loop processes worksheets, while the inner loop processes cells.

Nested loops can process the same range on multiple worksheets.

Dim ws As Worksheet
Dim rowNumber As Integer

For Each ws In ThisWorkbook.Worksheets

    For rowNumber = 1 To 10

        ws.Cells(rowNumber, 1).Value = "Processed"

    Next rowNumber

Next ws

Each worksheet is processed one at a time.

Nested loops are not limited to For loops. A Do loop can contain another Do loop.

Dim i As Integer
Dim j As Integer

i = 1

Do While i <= 3

    j = 1

    Do While j <= 3

        MsgBox i & " - " & j

        j = j + 1

    Loop

    i = i + 1

Loop

One type of loop can contain another type of loop.

Dim i As Integer
Dim j As Integer

For i = 1 To 3

    j = 1

    Do While j <= 3

        Cells(i, j).Value = i * j

        j = j + 1

    Loop

Next i

Here, a Do While loop is placed inside a For loop.

Nested loops can search through rows and columns.

Dim i As Integer
Dim j As Integer

For i = 1 To 20

    For j = 1 To 5

        If Cells(i, j).Value = "Rahul" Then

            MsgBox "Rahul found at " & _
                   Cells(i, j).Address

        End If

    Next j

Next i

When Exit For is used inside nested loops, it exits the For loop in which the statement is located.

Dim i As Integer
Dim j As Integer

For i = 1 To 5

    For j = 1 To 5

        If j = 3 Then
            Exit For
        End If

        Cells(i, j).Value = j

    Next j

Next i

Here, the inner loop stops when j = 3, while the outer loop continues.

Similarly, Exit Do exits the Do loop in which it is written.

Dim i As Integer
Dim j As Integer

i = 1

Do While i <= 5

    j = 1

    Do While j <= 5

        If j = 3 Then
            Exit Do
        End If

        Cells(i, j).Value = j

        j = j + 1

    Loop

    i = i + 1

Loop

Nested loops can be used to process marks for several students and subjects.

Dim studentRow As Integer
Dim subjectColumn As Integer

For studentRow = 2 To 11

    For subjectColumn = 2 To 6

        If Cells(studentRow, subjectColumn).Value < 40 Then

            Cells(studentRow, 7).Value = "Fail"

        End If

    Next subjectColumn

Next studentRow

The outer loop processes students and the inner loop checks each subject.

The following example creates a complete multiplication table from 1 to 10.

Sub CreateTable()

    Dim i As Integer
    Dim j As Integer

    For i = 1 To 10

        For j = 1 To 10

            Cells(i, j).Value = i * j

        Next j

    Next i

End Sub

This is a simple practical project for understanding nested loops.

Beginners commonly make these mistakes:

• Using the same counter variable for both loops.
• Forgetting the Next statement.
• Using the wrong row or column variable.
• Creating an unnecessary inner loop.
• Using Exit For without understanding which loop it exits.
• Processing too many rows and columns.
• Creating inefficient loops for large datasets.

The following example checks a range and replaces blank cells with zero.

Sub ProcessWorksheet()

    Dim rowNumber As Integer
    Dim columnNumber As Integer

    For rowNumber = 2 To 20

        For columnNumber = 1 To 5

            If Cells(rowNumber, columnNumber).Value = "" Then

                Cells(rowNumber, columnNumber).Value = 0

            End If

        Next columnNumber

    Next rowNumber

    MsgBox "Processing Completed"

End Sub

• Use different variables for different loops.
• Keep the inner loop simple when possible.
• Use meaningful variable names.
• Know exactly which rows and columns you want to process.
• Test nested loops with small ranges first.
• Use Exit For or Exit Do carefully.
• Avoid unnecessary nested loops for large datasets.
• Comment complicated nested loops.

A typical nested loop workflow is:

1. Start the outer loop.
2. Start the inner loop.
3. Perform the required operation.
4. Complete the inner loop.
5. Move to the next iteration of the outer loop.
6. Repeat until the outer loop finishes.

Dim rowNumber As Integer
Dim columnNumber As Integer

For rowNumber = 1 To 5

    For columnNumber = 1 To 5

        Cells(rowNumber, columnNumber).Value = _
        rowNumber * columnNumber

    Next columnNumber

Next rowNumber

Nested loops allow one loop to operate inside another loop. They are especially important when working with two-dimensional Excel data such as rows and columns.

Dim rowNumber As Integer
Dim columnNumber As Integer

For rowNumber = 1 To 5

    For columnNumber = 1 To 5

        Cells(rowNumber, columnNumber).Value = _
        rowNumber * columnNumber

    Next columnNumber

Next rowNumber

In this example, the outer loop controls the rows and the inner loop controls the columns. For every row, the inner loop processes all five columns.

Nested loops are commonly used in Excel VBA projects such as student result systems, marksheets, reports, data processing, table generation, and worksheet formatting.

The next lesson will introduce the Workbook Object, which is used to work with Excel workbooks through VBA.