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 specify the syntax's fixed frequency.
Before anything else, you must 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 the user can set this. 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 passes 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
Once you press the Play button and check the code, you will notice that 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 a second counter variable will handle the inner For loop. 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.