How to Use For Next Loop in Excel VBA (with 5 Examples)

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.

Syntax:

 Explanation:

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.

📌 Steps:

• 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.

📌 Steps:

• 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.

📌 Steps:

• 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.

📌 Steps:

• 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.

📌 Steps:

• 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.

📌 Steps:

• 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.

Conclusion

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.