Find Out More on Writing VBA For-Next Function in Microsoft Excel

There is certainly a point in your programming life that you have to repeat a certain task. Given that it will take a lot of time and lines of code to perform the tasks individually, this is when the loop function is useful.

For-Next is a considerable loop statement that will achieve your desired results. You can consider For-Next as the most commonly used VBA loop. Do you know that this loop statement can be highly useful in Microsoft Excel? You will find out about it by reading the following excerpt.

Steps on How to Use For-Next Statement

You can use the For-Next loop statement to establish a loop, which implies that you will be able to execute a code for a certain number of times. As a coder, you will also specify the fixed frequency for the syntax.

Before anything else, you have to be aware of certain elements when creating a For-Next loop statement. Given below are the most important terms to keep in mind:

Counter. This is the variable that will serve as the counter of the loop. Example syntax for this is the following:

Sub Loop Example ()

FOR counter = start To end [Step increment]

                  {…statements…}

NEXT [counter]

End Sub

 

Start. This is the counter’s starting value. In the given syntax above, you will replace the word start with a numerical value.

End. This is the counter’s ending value. Similar to start, you will also replace the word end with a numerical value when writing the actual code for the For-Next loop statement.

Increment. You must know that this can be set by the user. It is the value that the counter adds for every pass through the loop. By default, the increment’s value is one. This is why it can be an optional element.

Statements. The statements in the syntax above represent the codes to be executed whenever the loop is being passed through.

There are different types of loop you can work on. These are the following:

Single Loop

Sub SingleLoopExample()

For Number=1 To 6

MsgBox (Number)

Next Number

End Sub

 

If you will test the code by hitting the Play button, a message box will appear with the initial value of the variable number, which is one. Every time you will click the default OK button, the value will increase by one until you reach number six. This shows that in every click of the OK button, the code goes through the loop.

It is your choice to change the increment, which is one by default. The value of an increment can either be positive or negative.

An example of positive increment is the following:

Sub IncrementPositive()

For Number =1 To 11 Step 2

MsgBox (Number)

Next Number

End Sub

 

Once you have pressed the Play button, you will notice that the message box will not display consecutive numerical. Instead, in every press of OK button, you will get one, three, five, seven, nine, and 11 since you have changed the increment to two. Every pass through the loop will add two to the counter.

An example of negative increment is the following:

Sub IncrementNegative()

For Number=60 To 40 Step -5

MsgBox (Number)

Next Number

End Sub

 

You will notice that once you press the Play button and check the code, the message box will display numbers 60, 55, 50, 45, and 40. The results are decreasing instead of increasing for your increment is set to a negative value.

Double Loop

From the name itself, you will have two loops in this case. The outer For loop will be controlled by the first counter variable, whereas the inner For loop will be handled by a second counter variable. An example is provided below:

Sub DoubleLoop()

For Number = 2 to 6

       For Number2 = 9 To 11

       MsgBox Number & “and” & Number 2

Next Number2

Next Number

End Sub

 

The end result of the code is the sum of one, three, and five, which is nine. This will be displayed by the message box as well.

Now that you are aware of how For-Next loop statement works, you will be prepared to apply it when working with Microsoft Excel data.

 

Reference:

https://www.youtube.com/watch?v=n7zmlUowMYM&list=PL8i65Yy-xGEOloLZ5r1TJUEID92s5qlI7&index=4