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. 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. 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: 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. 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. 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.
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.
Trying to run VBA, But it occurs attached error. Pls help. https://uploads.disquscdn.com/images/ddf545f5157c16f1137bf3940f799825401ffde304aba74230c0b5d6bef980f3.jpg And Helpful Post………….
