For Next Loop in Excel VBA – A Beginner’s Guide with Examples

In VBA, looping is used when you need to perform the same task multiple times until a condition is met (or until a condition is true).

In this tutorial, you’ll learn how to use the For Next Loop in Excel VBA.

If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.

Using FOR NEXT Loop in Excel VBA

‘For Next’ Loop works by running the loop the specified number of times.

For example, if I ask you to add the integers from 1 to 10 manually, you would add the first two numbers, then add the third number to the result, then add the fourth number to the result, as so on..

Isn’t it?

The same logic is used in the For Next loop in VBA.

You specify how many times you want the loop to run and also specify what you want the code to do each time the loop is run.

Here is the For Next loop format you need to use in VBA to add the first 10 whole numbers.

For i = 1 to 10
[add the ith positive integer to the result]
Next i

Now let’s have a look at a few examples of how to use the For Next loop.

Example 1: Adding the First 10 Positive Integers

Below is the code that will add the first 10 positive integers using a For Next loop. It will then display a message box showing the sum of these numbers.

Sub AddNumbers()
Dim Total As Integer
Dim Count As Integer
Total = 0
For Count = 1 To 10
Total = Total + Count
Next Count
MsgBox Total
End Sub

In this code, the value of Total is set to 0 before getting into the For Next loop.

Once it gets into the loop, it holds the total value after every loop. So after the first loop, when Counter is 1, ‘Total’ value becomes 1, and after the second loop it becomes 3 (1+2), and so on.

And finally, when the loop ends and when Total has the sum of the first 10 positive integers, a MsgBox simply displays the result in a message box.

Click here to download the example file

Example 2: Adding the first 5 Even Positive Integers

To sum the first five even positive integers (i.e, 2,4,6,8, and 10), you need a similar code with a condition to only consider the even numbers and ignore the odd numbers.

Here is a code that will do it:

Sub AddEvenNumbers()
Dim Total As Integer
Dim Count As Integer
Total = 0
For Count = 2 To 10 Step 2
Total = Total + Count
Next Count
MsgBox Total
End Sub

Note that we started the Count value from 2 and also used Step 2 in the For syntax.

Step 2 would tell the code to increment the ‘Count’ value by 2 every time the loop is run. So the Count value starts from 2 and then becomes 4, 6, 8 and 10 as the looping occurs.

NOTE: Another way of doing this could be to run the loop from 1 to 10 and within the loop check whether the number is even or odd. However, using Step, in this case, is a more efficient way as it does not require the loop to run 10 times, but only 5 times.

Click here to download the example file.

Example 3: Get the Numeric Part from an Alphanumeric String

‘For Next’ loop can also be used to loop through each character in a string.

For example, if you have a list of alphanumeric strings, you can use the For Next loop to extract the numbers from it (as shown below):

Here is a code that creates a custom function in VBA that can be used like any other worksheet function.

It extracts the numeric part from the alphanumeric string.

Function GETNUMERIC(Cl As Range)
Dim i As Integer
Dim Result as Long
For i = 1 To Len(Cl)
If IsNumeric(Mid(Cl, i, 1)) Then
Result = Result & Mid(Cl, i, 1)
End If
Next i
GetNumeric = Result
End Function

In this code, the number of times the loop is run is dependent on the length of the alphanumeric string (it uses the LEN function to find the length of the string).

You need to put this function in the module code window, and then you can use it like any other worksheet function.

Click here to download the example file.

Example 4: Getting Random Numbers in the Selected Range

Suppose you want to quickly enter random numbers in the selected cells, here the code that will do it.

Sub RandomNumbers()
Dim MyRange As Range
Dim i As Integer, j As Integer
Set MyRange = Selection
For i = 1 To MyRange.Columns.Count
For j = 1 To MyRange.Rows.Count
MyRange.Cells(j, i) = Rnd
Next j
Next i
End Sub

This is an example of nested For Next loop where a For loop is used within a For Loop.

Suppose you make a selection of 10 rows and 4 columns, the value of i varies from 1 to 4 and the value of j varies from 1 to 10.

When the first For loop is run, the value of i is 1. It then moves to the second For loop which runs 10 times (for each row).

Once the second For loop has been executed 10 times, it goes back to the first For loop where now the value of i becomes 2. Again the next For loop runs for 10 times.

This is how the nested For Next loop works.

Click here to download the example file.

You May Also Like the Following Excel VBA Tutorials:

• Working with Cells and Ranges in Excel VBA.
• Working with Worksheets in Excel VBA.
• Working with Workbooks using VBA.
• Using IF Then Else Statements in VBA.
• Excel VBA Select Case.
• Creating a User-Defined Function in Excel.
• Excel VBA Events – An Easy (and Complete) Guide
• How to Record a Macro in Excel.
• How to Run a Macro in Excel.
• How to Create an Add-in in Excel.
• How to Save and Reuse Macro using Excel Personal Macro Workbook.
• Useful Excel Macro Examples for Beginners.
• Using InStr Function in VBA.