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"

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
 VBA For Next loop - basic example
VBA For Next loop – basic example

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

End Sub
VBA for next – even numbers

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
VBA For Next – even numbers from 0 to 50

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.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: