For Loop in VBA – Excel

In the previous article, we learned about the For Each Loop. Today we will learn about another important loop used in VBA which is For Loop. We would learn about the implementation of For Loop with the help of examples. This article also covers the nesting of For Loop.

So let us begin learning.

What is For Loop?

Loops in any programming language are used to repeat the lines of code. The For Loop in VBA is also used for repeated execution of a set of statements multiple times. We can use the For Loop only when we know exactly how many times the loop must iterate the block of code inside it.

The For Loop requires a counter variable that iterates the loop the required number of times. The counter variable has a starting and ending value. There is a step value which is 1 by default. The step value is added to the counter variable once the current iteration of the loop ends. It is used to update the value of the counter variable at the end of each iteration of the loop until we reach the maximum limit of the counter variable.

When the For loop iterates the block of code the number of times it was supposed to, then we can say that the loop is matured.

Syntax of For Loop

For <counter_name>=<start> To <end> [Step <step_value>]
'statement 1
'statement 2
'
'
'statement n
Next [<counter_name>]

The parts in square brackets [Step <step_value> and [<counter_name>] in this syntax are optional.

The following list explains the parts of For Loop syntax in VBA.

• <counter_name> – This is an integer variable whose numerical value is used as Loop Counter. This variable cannot be a boolean or an Array type.
• <start> – This is the initial value of the counter variable.
• <end> – This is the final value of the counter variable.
• <step_value> – This is the value that will be added to the counter variable at the end of each iteration. It can be a negative value. The default value of the step variable is 1. This implies that the counter variable is incremented by 1 at the end of each repetition of the For Loop.

Examples to Implement For Loop in VBA

In this section of the blog, we would learn to implement the For Loop with the help of Sub Procedures.

Example 1 – Single Loop to Enter Cell Values in Worksheet

Let us suppose we wish to write the consecutive Dates starting from Today’s Date in the Cells from cell A1, A2,A3 up to A5

We would use the following Sub Procedure to do this action.

Sub insertDateInCell()
'Single Loop to Enter Cell Values in Worksheet
Dim todayDate As Date
'todayDate contains the system's Date
todayDate = Date
'loop from 1 to 5
    For i = 1 To 5 Step 1
        'cells refer to the cell whose value we wish to write, i is counter variable
        Cells(i, 1) = Date + i - 1
    Next i
End Sub

When you run this Sub Procedure, as a result cell A1 would contain today’s date which is 5-2-2023 and the cells one row down up to 5th row would contain next dates with a gap of one day.

Explanation – We have used the for loop that goes from i=1 to 5. The value of i is used each iteration as the row number in Cells property Cells( i, 1 ). Column remains 1 but the row is updated with each iteration to move one row down the current cell. The value of counter variable i is also added to Date to increment the Date by 1 in each iteration.

Example 2 – Nesting of two For Loops

When we wish to add data to a two dimensional range, we need to have one loop nested inside the other. We have an outer loop that contains a loop inside it which is inner loop. The inner loop is executed the number of times the outer loop is iterated. We will take different counter variables for both loops.

We would run the Outer for loop from i=1 to row_count. For each row ( each iteration of outer loop ), we would run the inner loop from j=1 to column_count to write in columns to the left of first cell of a row.

Let us suppose we wish to write cell values to the range A1:C4. In this range, the rows begin from 1 to 4 while the columns start from A to C. We would use numerical value for columns, which is 1 to 3.

Use the following Sub Procedure to write in range A1:C4.

Sub insertDataIn2DRange()
'inserting data in 2D range using nested for loop
Dim row_count As Integer
Dim col_count As Integer
'row_count and col_count contain total number of rows and columns in range A1:C4
row_count = Range("A1:C4").Rows.Count
col_count = Range("A1:C4").Columns.Count
'looping for row 1 to 4
For i = 1 To row_count
    'looping for each row, column from A to C
    For j = 1 To col_count
            'cells property to refer to the cell
        Cells(i, j) = InputBox("Enter value of " & Cells(i, j).Address)
        Cells(i, j).Interior.ColorIndex = 24
    Next j
Next i
End Sub

When you run this Sub Procedure, you will be asked for the cell values of each cell in range A1:C4.

Once you enter the cell value, it would change the interior color for that cell.

This brings us to an end.

Thank you for reading.