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.

**Table of Contents**hide

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

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