For loop in Excel VBA

The VBA for loop is used to execute the group of statements for the given number of times. For example, you have ten cells in an Excel sheet and require to read the numeric value in each cell and multiply by 10 or apply a formula.

You may use a For..Next loop to iterate through each cell (from 1 to 10). On each iteration, multiply the cell value by 10 ( or perform any other desired action) and assign the result to the second column cells. I will show you examples of that let us first look at the syntax of for loop.

Structure of using for..Next loop

Following is the general syntax of using the for loop in Visual Basic for Applications:

For counter  = start To end [ Step step ]

[ statements to execute here]

Next [ counter ]

Where:

• Counter is the numeric variable that is required.
• The Start sets the initial value of the Counter variable.
• The End specifies the end value. As the counter reaches this value, the loop should be terminated.
• Step defines the value of the Counter variable incremented or decremented in each loop.
• You may place a group of statements to be executed on each iteration.
• The Next keyword is required that terminates the definition of the for loop.
• If you require exiting the loop at any stage, use the Exit statement.

Note: You should use a while or do (while and until) loop if you have no idea of the number of iterations a loop will do. The for loop should be used if you know in advance how many times a loop will run.

An example of displaying numbers in Excel sheet by using for loop

In the following example, a for loop is used to display the numbers from 1 to 10 to the ten cells in A column. Have a look:

This is how the for loop worked in the example:

You can see, the for loop code is written in the click event of the ActiveX button. The button is placed on the Excel sheet. In the for loop, the initial value of the counter variable is set as 2.

For x = 2 To 10 Step 1

The step is 1, so x will be incremented by 1 on each iteration of the loop.

This line of code will update the excel cell by the value of x:

Cells(x, 1).Value = x

The first cell in the Excel sheet is given the heading text.

The Next x statement incremented the value of x by 1. As long as the value of x was less than or equal to 10, the loop continued. As it reached the value 11, the condition became false and for loop terminated.

An example decrementing the counter

In this example, the value of the counter is decremented on each iteration of the for loop. The initial value of the Counter variable x = 100. On each iteration, it is decremented by 10. The current value of the Counter variable is set in the excel cell like the above example:

The output:

The value of x is displayed from 100 to 10 with a gap of 10 in excel cells.

An example of Fahrenheit to Celsius with for loop

The cells in column A (2 to 7) contain the Fahrenheit temperatures while the adjacent cell in column B is empty initially. As the button is pressed, the for loop will execute that takes the values of Fahrenheit temperatures. The Fahrenheit to Celsius formula is applied in each iteration.

The result is updated on the related cell in column B. Following is the program:

A demo of using nested for loop

You may also use an inner for loop inside an outer loop. This is called VBA nested for loop. In this case, the inner loop is executed for each iteration of the outer for loop. Have a look at the code below for learning more about this:

The output of the code:

There, the outer loop updates column A’s specified cell by the variable i’s value. The inner loop iterates three times and updates the cells by 1,2,3. The loop goes on till the value of the variable i reaches 16.

The example of omitting the current iteration – Continue alternate

VBA has no continue statement which is used to omit current iteration in various programming languages.

If you require omitting the current iteration, you may use different solutions for that. One of the solutions is shown in the example below. A for loop is supposed to run from numbers 2 to 11 with an increment of 1 on each iteration of the loop.

The current value will be updated on the excel sheet, however, for values 3,5, and 7 the loop will omit the current iteration without updating the Excel cell. Have a look:

Using the exit statement in for loop VBA

As mentioned earlier, use the Exit For statement for eliminating the loop and moving the execution next line after the For loop. See the usage of Exit For in the following example:

The result:

You saw, the loop was supposed to run until the value of x reaches 10, however, it ended as it reached 5 by using the Exit For statement.