Loop is used when we need to run an operation operate repeatedly. Without performing the same operation manually every time in Excel, we can apply the VBA loop operation. Different loops exist in Excel VBA. In this article, we will discuss the VBA For Next loop in Excel in detail with proper illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to VBA For Next Loop in Excel
The VBA For Loop is a loop that runs an operation a specific number of times. After running the loop a specific number of times, the program will stop automatically. For this reason, it is called a fixed loop.
For counter = Start to End [Step]
|counter||It is a numeric variable.|
|start||It indicates the starting value of the counter.|
|end||It defines the final value of the counter.|
|step||This is not a mandatory option. After each time running a code, the value of the counter changes. This changing amount depends on this attribute.|
|statements||This is also not mandatory. One or more statements may exist in a VBA code. The statements define the operations those needs to be performed in a loop.|
|next||This statement orders to run the counter again with the stepsize and runs until it reaches the ending point.|
5 Examples of Using VBA For Next Loop in Excel
We already discussed the attributes of the VBA For Next loop. Now, we will show some examples to understand the use of this loop in Excel VBA.
1. Show 1st 10 Numbers
In this example, we show how to enter the VBA first. Then, we will apply a VBA code to show 1st 10 positive numbers.
- First, we enter the worksheet and go to the bottom of the sheet.
- We will see the Sheet name Press the right button of the mouse.
- Choose the View Code option from the Context menu.
- Now, choose the Module option from the Insert tab.
- Finally, we enter the VBA window.
We will write VBA code in this window and run them.
- Copy the following VBA code and paste it into the VBA window.
Sub Show_1st_10_Numbers() Dim n As Integer For n = 1 To 10 ActiveCell.Value = n ActiveCell.Offset(1, 0).Activate Next n End Sub
- Select Cell B5 and press F5 to run the VBA code.
Numbers from 1 to 10 are shown on the sheet.
Here, we set the value of n from 1 to 10, which is the counter. No step size has been given. So, it will choose 1 as the default step size. Also, the Offset function is used. That will show the numbers in a column one after another.
2. Get Even Numbers from 1 to 20
We can get the even or odd numbers from a given range using this For Next loop. In this example, we will find out the even numbers using a Mod function with the For Next loop.
- Choose Cell B5 first.
- Now, copy the VBA code below and paste it into the VBA window.
Sub Show_Even_Numbers() Dim n As Integer For n = 1 To 20 If n Mod 2 = 0 Then ActiveCell.Value = n ActiveCell.Offset(1, 0).Activate Else End If Next n End Sub
- Now, press the F5 button and look at the worksheet.
We can see all even numbers in the range 1 to 20 are shown here. We used the Mod function in the For loop, which detects the even numbers.
3. Sum the 1st 10 Integers
Here, we will find out the Sum of 1st 10 integers using the For Next loop.
- Just put this VBA code on the module.
Sub Sum_of_Numbers() Dim Sum, n As Integer n = 10 Sum = 0 For n = 1 To n Sum = Sum + n Next n MsgBox "Total: " & Sum End Sub
- Then run this code by pressing the F5 button.
Here, we initially assume the value of the Sum is zero. Then, the add present value of Sum with the value of n using a For Next loop.
4. Apply For Next Loop Twice in Excel
Previously, we showed the use of a single For Next loop in Excel. Here, we will apply a double For Next loop, that will set the value of selected cells.
- We want to set the value of the table of dimension 5×5. So, put the following code on the VBA window.
Sub Double_loop() Dim n As Integer, m As Integer For n = 4 To 8 For m = 2 To 6 Cells(n, m).Value = 5 Next m Next n End Sub
- Press the F5 button and the code will run.
We can see value 5 is set on a 5×5 table. As we apply double for loop the code become able to set the value in the row and column both way.
5. Apply For Next Loop Thrice
In this example, we will apply a triple loop to perform 3 tasks that are co-related. We want to set the value of certain cells from a variable.
- Copy the VBA code of the triple For Next loop with step size 2.
Sub Triple_loop() Dim n, m, p As Integer p = 5 For n = 4 To 8 For m = 2 To 6 For p = 5 To p Step 2 Cells(n, m).Value = p Next p Next m Next n End Sub
- Now, press the F5 and run the code.
This will set values by utilizing the variable c with step size 2 in the cells got from the rest of the two loops.
How to Exit For Loop in VBA
In this section, we will show the use of the VBA Exit For in Excel. VBA For loop stops when the counter reaches the assigned maximum. But you can add an Exit For statement to exit a For Loop before the assigned maximum count is reached when meeting other criteria.
VBA Code to Apply:
Sub Number_to_Limit() Dim n As Integer Dim wrd As String wrd = "Alok" For n = 1 To 10 ActiveCell.Value = wrd ActiveCell.Offset(1, 0).Activate If ActiveCell = "Stop" Then Exit For End If Next n MsgBox "Stopped" End Sub
The macros will stop running when they meet the word “Stop” on the way, even though the 10th iteration is not completed yet. Give that a shot!
Excel VBA Do While Loop: Alternative to For Next
The operation of the Do While Loop is quite similar to For Next loop. This Do While loop is used when a repeated process is needed. It runs the operation until the given condition is true. Here is an example of the Do While loop to input 1st 10 integers numbers in Excel.
- Copy the following VBA code on the VBA window.
Private Sub Show_1st_10_Integers() Do While n < 10 n = n + 1 ActiveCell.Value = n ActiveCell.Offset(1, 0).Activate Loop End Sub
- Select Cell B5 and run the code by pressing the F5 button.
We can see numbers from 1 to 10 has inserted.
In this article, we discussed the VBA For Next Loop in Excel. We explained with examples. We also added some other VBA related to this For Next loop. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.