VBA For Next Loop

For Next VBA loop – basic information

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

The operation scheme itself looks as follows:

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

Example of the For Next loop

We already know what the Next Next VBA loop is. Let’s try to make a slightly more difficult example using this loop. Our 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.

 

In the example, we displayed even numbers in the range from 0 to 100. We also used the Step interval to increase the counter by 2 each time the loop repeats. As we can see, our numbers have entered the spreadsheet in the simplest way, and thus in every other cell. We did so to test the operation of the Step interval in our loop. Let’s remodel our code, so that even numbers are displayed in successive rows.

As with the Do Loop loop, the For Next VBA loop can be terminated before the scheduled execution indicated by the meter. We use the phrase Exit For .

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: