VBA For Next Loop

Last Updated on January 11, 2023 by token

VBA For Next loop – basic informations

The VBA For Next Loop is another loop that we will learn. In the simplest words, the loop allows us to enter in advance the number of repetitions of instructions to be done. For example, let’s try to use VBA For loop to display the “For Next Loop Example” text in cells from “A1” to “A10” in the Excel data sheet.

Sub ForNextExample()

Dim intRow As Integer

For intRow = 1 To 10
 Cells(intRow, 1) = "For Next Loop Example"
Next

End Sub

The operation scheme looks as follows:

For Counter = start to end [Step interwal] 
 [code to execute] 
Next [Counter] 

• For – keyword, starts the For Next loop
• Counter – determines the number of loop repetitions
• start – The starting number of the counter, ie. 1
• end – The ending number of the counter, ie. 10
• [Step interval] – You can set it optionally, for example if we want the loop to increase by a different number than 1 during the repetition.
• [code to execute] – Code block to be executed as part of the loop
• Next – word closing the loop
• [counter] – optional word. It means that the counter should be increased within the loop

Example of the VBA For Next loop

You already know what the Next VBA loop is. Let’s try to make a slightly more difficult example using this loop. This task will be to display a series of numbers as in the previous examples. They will be even numbers in the range from 0 to 100.

Sub ForNextExample2()

Dim intCounter As Integer

For intCounter = 1 To 100 Step 2
 Cells(intCounter, 1) = intCounter - 1
Next

End Sub

In this example, you displayed even numbers in the range from 0 to 100. You also used the Step interval to increase the counter by 2 each time the loop repeats. As you can see, your numbers have entered the spreadsheet in the simplest way. Let’s remake our code, so that even numbers are displayed in right rows.

Sub ForNextExample3()

Dim intCounter As Integer

For intCounter = 1 To 51 Step 1
 Cells(intCounter, 1) = (intCounter - 1) * 2
Next intCounter

End Sub

As with the Do While loop, the For Next VBA loop can be terminated before the scheduled execution indicated by the counter. You can use the phrase Exit For for it.

Tasks (You can enter the solution in a comment)

1. Create a multiplication table using the For … Next loop
2. Create a simple table using the For … Next loop, which generates a list of people in the form of person1, person2, person3 … etc. In the second column, use the RND () and loop function to add the age of the person from 18 to 65. The list should contain 50 items.