For loop in VBA

The For Loop is one of the most frequently used loop in Excel VBA. The For Loop starts with the "For" construct and ends with the "Next" construct as shown below. The following loop uses x as a counter and loops through starting from 1 up to the point where x is 10. Each time, x is increment by the value of 1.

Basic Construct


	Dim x As Integer
	For x = 1 To 10

	Next x

Nested For loop

The For loop can be easily nested by using two variables x and y as shown below:


	Dim x As Integer
	Dim y As Integer
	For x = 1 To 10
		For y = 1 To 10	

		Next y
	Next x

Using the For loop to initialize a range of cells in Excel

The following is a commonly used For loop for initializing cells in Excel.


	Sub InitCells()

	Dim x As Integer
	For x = 1 To 10
		Cells(x, 1) = x
	Next x
                                                           
	End Sub

For loop Step increment

In the previous examples, the For loop increment the value of x by 1 each time. It is possible to use a different increment value. For example, the For Loop below increment the value of x by 2 after each loop.


	Dim x As Integer
	For x = 1 To 10 Step 2
		Cells(x, 1) = x
	Next x

Exit the For loop

It is possible to exit a For loop when certain conditions are ture by using the Exit keyword. This is illustrated below:


	Sub InitCells()

	Dim x As Integer
	For x = 1 To 10
		Cells(x, 1) = x
		If x = 5 Then Exit For
	Next x
                                                           
	End Sub

Continue the For loop

If you want to skip some of the loops in a For loop, you can use a If statement.


	Sub InitCells()

	Dim x As Integer
	For x = 1 To 10
		If x <= 5 Then
		    Cells(x, 1) = x
		End If
	Next x                                                           
	End Sub

You can also use the GoTo statement as shown below. When the value of x is greater than 5, it will skip the immediate next line as the Goto statement is executed.


	Sub InitCells()

	Dim x As Integer
	Dim y As Integer
	For x = 1 To 10
		If x > 5 Then GoTo ContinueLoop
		Cells(x, 1) = x
	ContinueLoop:
	Next x                                                           
	End Sub