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:

For counter = Start to End [Step]

Statements

Next counter

 Explanation:

Attribute Description
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.

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

Process to enter VBA window from the Context menu

  • Now, choose the Module option from the Insert tab.

Go to VBA module 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

Put a VBA macro based on For Next loop to get 1st 10 integers

  • 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

VBA code of For Next loop for Even numbers

  • 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

Get sum of 1st 10 integers from applying Excel VBA code

  • 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

Apply double for next loop

  • 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

Apply triple for next loop

  • 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

Use of VBA DO While loop in Excel

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

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo