Loop

The Excel VBA loop (or For Next loop) is a very useful programming statement which is often used in Excel VBA. First, we will look at an easy example of how to loop through a one-dimensional range.

Single Loop

You can use a single loop to loop through a one dimensional range.

Place a command button on your worksheet and add the following code lines:

1. The first two code lines declare two variables of type Integer. One named total and one named i.

2. Next, we initialize the variable total with value 0.

3. Add the For Next loop which runs from 1 to 4.

4. Create an If Then statement which increments total by 1 if a value is higher than 40.

5. Finally, use a message box to display the total number of values higher than 40.

Result when you click the command button on the sheet a few times:

Explanation: The code lines between For and Next will be executed four times. For i = 1, 2, 3 and 4. For i =1, Excel VBA fills in 1 for i and gets Cells(1,1).value. This is the first value. When Excel VBA reaches Next i, it jumps back to the For statement increasing i with 1. For i = 2, Excel VBA fills in 2 for i and gets Cells(1,2).value. This is the second value. For i = 2, Excel VBA also increments total by 1 because the second value is higher than 40. For i = 3, etc.

Excel VBA loops through the code four times and after that leaves the For Next loop and executes the rest of the code.

Result:

Note: it is good practice to always indent (tab) the code between the words For and Next. This makes your code easier to read.

Did you find this information helpful? Show your appreciation, vote for us.

Go to Top: Loop    |    Go to Next Topic: Logical Operators