For...Next Loop in VBA

A For...Next loop repeats a block of VBA code a specified number of times.

The loop uses a counter variable that changes during each repetition.

Basic structure:

For counter = start To end
    statements
Next counter

For...Next is useful when the same operation needs to be performed repeatedly.

For example:

• Print numbers from 1 to 10.
• Fill Excel cells with values.
• Calculate marks for multiple students.
• Format multiple rows.
• Process a list of records.
• Generate repeated reports.

The basic syntax is:

For counter = start To end

    statements

Next counter

The counter starts at the specified start value and continues until it reaches the end value.

The following loop displays numbers from 1 to 5.

Dim i As Integer

For i = 1 To 5

    MsgBox i

Next i

The loop runs five times with values 1, 2, 3, 4, and 5.

The counter variable keeps track of the current iteration of the loop.

Dim i As Integer

For i = 1 To 5
    MsgBox i
Next i

Here, i is the counter variable. Its value changes automatically during each iteration.

A For...Next loop needs a starting value and an ending value.

For i = 1 To 10
    MsgBox i
Next i

Here:

• 1 is the starting value.
• 10 is the ending value.
• i is the counter.

For...Next is very useful for writing values into Excel cells.

Dim i As Integer

For i = 1 To 5

    Cells(i, 1).Value = i

Next i

This writes numbers 1 to 5 into cells A1 to A5.

You can also write text repeatedly into cells.

Dim i As Integer

For i = 1 To 5

    Cells(i, 1).Value = "Student"

Next i

The word Student is written into cells A1 through A5.

A loop can perform calculations repeatedly.

Dim i As Integer

For i = 1 To 10

    Cells(i, 1).Value = i * 10

Next i

The worksheet will contain 10, 20, 30, and so on up to 100.

The Step keyword controls how much the counter changes after each iteration.

Dim i As Integer

For i = 1 To 10 Step 2

    MsgBox i

Next i

The values will be 1, 3, 5, 7, and 9.

Step 1 increases the counter by one each time.

Dim i As Integer

For i = 1 To 5 Step 1

    MsgBox i

Next i

Step 1 is also the normal default behavior of a For...Next loop.

A negative Step can be used to count backwards.

Dim i As Integer

For i = 5 To 1 Step -1

    MsgBox i

Next i

The values are 5, 4, 3, 2, and 1.

You can use a For loop to process rows in a worksheet.

Dim i As Integer

For i = 2 To 10

    Cells(i, 1).Value = "Student " & i

Next i

This writes student labels into rows 2 through 10.

The loop can also be used with columns.

Dim i As Integer

For i = 1 To 5

    Cells(1, i).Value = i

Next i

This writes numbers into cells A1 through E1.

For...Next can be used to process student records.

Dim i As Integer

For i = 2 To 11

    Cells(i, 4).Value = "Active"

Next i

This sets the status to Active for rows 2 through 11.

A loop can calculate or process marks for multiple students.

Dim i As Integer

For i = 2 To 6

    Cells(i, 3).Value = Cells(i, 1).Value + Cells(i, 2).Value

Next i

This adds the values in columns A and B and stores the result in column C.

A For...Next loop can contain an If statement.

Dim i As Integer

For i = 2 To 10

    If Cells(i, 2).Value >= 40 Then
        Cells(i, 3).Value = "Pass"
    Else
        Cells(i, 3).Value = "Fail"
    End If

Next i

This checks the marks of multiple students.

MsgBox can be used inside a loop to display repeated messages.

Dim i As Integer

For i = 1 To 3

    MsgBox "Welcome Student " & i

Next i

Three message boxes will be displayed.

A variable can be used to store a calculation inside the loop.

Dim i As Integer
Dim total As Integer

total = 0

For i = 1 To 5

    total = total + i

Next i

MsgBox total

The final result is 15.

You can create a multiplication table using a For...Next loop.

Dim i As Integer
Dim number As Integer

number = 5

For i = 1 To 10

    Cells(i, 1).Value = number & " x " & i
    Cells(i, 2).Value = number * i

Next i

This creates the table of 5 in columns A and B.

For...Next can be used to format multiple cells.

Dim i As Integer

For i = 1 To 10

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

Next i

This makes cells A1 through A10 bold.

You can format an entire row during each iteration.

Dim i As Integer

For i = 2 To 10

    Rows(i).Font.Bold = True

Next i

Rows 2 through 10 will have bold text.

A loop can process cells within a specific range.

Dim i As Integer

For i = 1 To 10

    Range("A" & i).Value = i * 100

Next i

This writes 100, 200, 300, and so on into column A.

A For loop is useful for automatically numbering records.

Dim i As Integer

For i = 2 To 11

    Cells(i, 1).Value = i - 1

Next i

Rows 2 to 11 will receive serial numbers 1 to 10.

The following example checks marks for multiple students and assigns Pass or Fail.

Dim i As Integer

For i = 2 To 11

    If Cells(i, 2).Value >= 40 Then

        Cells(i, 3).Value = "Pass"

    Else

        Cells(i, 3).Value = "Fail"

    End If

Next i

This can process ten student records automatically.

Beginners commonly make these mistakes:

• Forgetting Next.
• Using the wrong starting or ending value.
• Using an incorrect counter variable.
• Creating an endless or unexpectedly long loop.
• Using the wrong row or column number.
• Forgetting that Excel row and column numbers start at 1.

Correct structure:

For i = 1 To 10
    MsgBox i
Next i

A loop can automatically create a simple list of student IDs.

Dim i As Integer

For i = 2 To 11

    Cells(i, 1).Value = "STU" & (i - 1)

Next i

This creates IDs such as STU1, STU2, STU3, and so on.

• Use meaningful counter variables when possible.
• Clearly define the starting and ending values.
• Keep the loop body simple.
• Test the loop with a small number first.
• Use Step when you need a custom increment.
• Be careful when changing worksheet rows inside the loop.
• Use comments for complex loops.

A typical For...Next workflow is:

1. Declare a counter variable.
2. Specify the starting value.
3. Specify the ending value.
4. Write the statements to repeat.
5. Use Next to move to the next iteration.
6. Use Step when a different increment is required.
7. Test the loop with different ranges.

Dim i As Integer

For i = 1 To 5

    Cells(i, 1).Value = i

Next i

The For...Next loop is one of the most important looping structures in VBA. It allows you to repeat the same block of code a specific number of times.

It is especially useful when working with Excel rows, columns, student records, marksheets, calculations, formatting, and automated data processing.

Dim i As Integer

For i = 2 To 11

    If Cells(i, 2).Value >= 40 Then

        Cells(i, 3).Value = "Pass"

    Else

        Cells(i, 3).Value = "Fail"

    End If

Next i

In this example, the loop processes rows 2 through 11 and checks each student's marks. This demonstrates how loops and conditional statements can work together in a practical Excel VBA application.