VBA : For - Next Loop

It allows you to execute code repeatedly until a certain condition is reached.

The syntax for FOR NEXT loop is as follows :

For < Counter Variable>= <Initial Value> To <End Value>
<Code that you want to repeat through loop>Next <Counter Variable>

The example is shown in the image below :

Examples

1. Write series of numbers

The following code writes values from 1 to 5 in column A :

Sub forloop()
Dim i As Integer
For i = 1 To 5
Range("A" & i).Value = i
Next i
End Sub

In the first loop counter, Range("A" & i) evaluates to Range("A"&1) i.e. Range("A1"). In the second loop counter, Range("A" & i) evaluates to Range("A"&2) i.e. Range("A2"). Then the range increments by 1 till fifth row.

The output is shown in the image below :

2. Multiply column C values by 2

The data is shown in the image below :

Sub Dynamic()
Dim last As Double, i As Integer
With ActiveSheet
last = .Cells(.Rows.Count, "C").End(xlUp).Row - 2
End WithFor i = 1 To lastRange("D" & i + 2).Formula = Range("C" & i + 2).Value * 2Next i
End Sub

Explanation :

1. The following code identifies the last used row in column C. 2 is substracted from the code because column C contains blank row and header at the beginning of the column.

With ActiveSheet
last = .Cells(.Rows.Count, "C").End(xlUp).Row - 2
End With

2. In the first loop counter, Range("D" & i + 2) evaluates to Range("D"&1+2) i.e. Range("D3"). Then the range increments by 1 till last used row.

3. Filling a column with consecutive numbers in consecutive cells

You can increment counter variable by any value you like. The Step keyword tells excel to increment counter variable by the specified value.

Sub forloopstep()
Dim i As Integer For i = 1 To 10 Step 2
Range("A" & i).Value = i
Next i
End Sub

The Step 2 code tells excel toincrement i each time through the loop by 2.

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.