For Loop in Excel VBA
Looping is a technique to repeat tasks efficiently. A loop in Excel VBA repeats actions, saving time by processing many cells with minimal code.
Single Loop
A single loop is sufficient to go through each cell in a one-dimensional range.
Insert a command button on your worksheet, then enter the following code:
Dim i As Integer For i = 1 To 6 Cells(i, 1).Value = 100 Next i
The result of clicking the command button:
Explanation: The For-Next loop runs six times. At the start of the loop, when i is 1, Excel puts 100 in the first cell (A1). When Next i is executed, Excel VBA adds 1 to i and goes back to evaluate the For loop condition again. If i is 2, then Excel VBA writes the number 100 into the cell in the second row and first column.
Note: Indenting the code between For and Next improves readability. It clearly shows which statements belong to the loop, making your code easier to follow and maintain.
Double Loop
A double loop helps you go through rows and columns in a two-dimensional cell range.
Insert a command button into your worksheet, then enter the code below:
Dim i As Integer, j As Integer For i = 1 To 6 For j = 1 To 2 Cells(i, j).Value = 100 Next j Next i
The result of clicking the command button:
Explanation: If i and j are both 1, Excel puts 100 in the first cell (A1). When the code reaches “Next j”, it adds 1 to the value of j and goes back to the For loop to continue. Now, i = 1 and j = 2 so Excel VBA places the value 100 in the cell located at row 1, column 2.
Because J has reached the last value (j=2) in the loop, VBA skips the Next J. Then it execute Next i, increases i by 1, and restarts the loop. For i = 2 and j = 1, the program sets the value 100 in cell located at row 2, column 1.
Triple Loop
A triple loop allows you to cycle through multiple two-dimensional ranges across different Excel worksheets, checking or modifying cells on each sheet efficiently.
Insert a command button onto your worksheet, then enter the following code:
Dim c As Integer, i As Integer, j As Integer For c = 1 To 3 For i = 1 To 6 For j = 1 To 2 Worksheets(c).Cells(i, j).Value = 100 Next j Next i Next c
Explanation: The only difference from the double loop code is the addition of a third loop. We also used Worksheets(c).Cells to refer to cells on a specific worksheet. In this case, c = 1 for the first sheet, c = 2 for the second, and c = 3 for the third.
Do While Loop
Besides the For Next loop, you can use other loop types in VBA. For example, the Do While Loop. The code written between Do While and Loop keeps running as long as the condition after Do While is true.
1. Insert a command button on your worksheet, then enter the following code:
Dim i As Integer i = 1 Do While i < 6 Cells(i, 1).Value = 20 i = i + 1 Loop
The result of clicking the command button:
Explanation: In the given Excel VBA code, the loop executes repeatedly while i remains less than 6. During each loop, the number 20 is placed in cell (i, 1), and i increases by 1 until the condition fails. In Excel VBA (and other programming languages), the ‘=’ symbol means that a value is assigned to something. It does not mean equal. The loop repeats until i reaches 6, but the loop stops before running for i = 6, so the value 20 is placed in column A five times.
2. Enter some numbers in column A.
3. Insert a command button on your worksheet, then enter the following code:
Dim i As Integer i = 1 Do While Cells(i, 1).Value <> "" Cells(i, 2).Value = Cells(i, 1).Value + 10 i = i + 1 Loop
The result of clicking the command button:
Explanation: This VBA line checks whether the cell at position (i, 1) is not blank (symbol < > represents “not equal to”). If it has a value, then it adds 10 to that value and enters the result into Cells (i, 2). Excel VBA stops at i = 7 due to the empty A7 cell. It is a useful technique for looping through any number of rows in a worksheet.
1/12 Completed! Learn much more about loops ➝
Next Chapter: Macro Errors