A basic For loop in VBA repeats a block of code a set number of times. Write For counter = start To end, place the repeated instructions underneath, and finish with Next counter; VBA increases the counter by 1 unless you specify a different Step.
What a For loop does
A For...Next loop is useful when you know how many repetitions are needed, or when you want to process a sequence of numbers. The counter starts at a chosen value, changes by a fixed increment after each pass, and determines when the loop ends. For example, a loop from 1 to 5 runs five times, with the counter taking the values 1, 2, 3, 4, and 5.
The instructions between For and Next are called the loop body. They can calculate values, update worksheet cells, or call other procedures. A loop can save you from writing the same instruction repeatedly, and it makes the repetition rule explicit. It does not automatically know which worksheet or range you mean: your code still needs to identify the workbook objects it should use.
In Excel for Microsoft 365 on Windows, VBA code is typically written in the Visual Basic Editor and run as a macro. The loop language itself is part of VBA, so its basic syntax is the same in Excel for Mac. Macros are not supported in Excel for the web, although a workbook containing VBA can be opened there.
Basic For…Next syntax
The general structure is:
Sub CountToFive()
Dim counter As Long
For counter = 1 To 5
Debug.Print counter
Next counter
End Sub
Dim counter As Long declares the loop variable as a whole number. For counter = 1 To 5 sets the first and last values. Debug.Print sends each value to the Immediate window in the Visual Basic Editor, and Next counter advances the loop and returns execution to the loop body if another pass is due.
The start and end values are included when the loop counts in the usual direction. This means For counter = 1 To 5 has five passes, not four. When the loop is finished, execution continues with the first statement after Next. Naming the variable after Next is optional in VBA, but including it makes longer or nested loops easier to read.
VBA also permits expressions in place of literal numbers, such as variables or calculated values. If you use an expression that depends on a worksheet, qualify the worksheet reference so the code does not accidentally read from whichever sheet happens to be active.
Change the increment with Step
Use Step to control how much the counter changes each time. For instance, with a loop from 2 to 10 and Step 2, the counter takes the values 2, 4, 6, 8, and 10. A step of 1 is assumed when you leave out the clause.
Sub CountByTwos()
Dim counter As Long
For counter = 2 To 10 Step 2
Debug.Print counter
Next counter
End Sub
A negative step counts downward. In that case, make the starting value higher than the ending value; for example, For counter = 5 To 1 Step -1 runs with values 5, 4, 3, 2, and 1. If the start, end, and step do not point in a compatible direction, the loop body may not run at all. A zero step is not a useful way to repeat a loop and causes a VBA error.
Choose a step that matches the values you intend to visit. A step of 3 from 1 to 10 visits 1, 4, 7, and 10 only if the final value is reached by repeated increments; here it is not, so the actual values are 1, 4, and 7. VBA does not adjust the last value to force it to equal the endpoint.
Example: write numbers into worksheet cells
This example writes the numbers 1 through 10 into cells A1 through A10 on a worksheet named Report. It uses a separate row variable to translate the counter into a cell address.
Sub FillReportNumbers()
Dim counter As Long
Dim reportSheet As Worksheet
Set reportSheet = ThisWorkbook.Worksheets("Report")
For counter = 1 To 10
reportSheet.Cells(counter, 1).Value = counter
Next counter
End Sub
Cells(row, column) takes numeric row and column indexes, so Cells(counter, 1) refers to column A at the row represented by counter. ThisWorkbook means the workbook containing the macro, not necessarily the workbook currently active in Excel. If the workbook does not contain a sheet named Report, VBA will stop with a subscript error. Replace the name with an existing worksheet name or add suitable error handling for a reusable macro.
To start writing at row 2 instead, you could loop from 1 to 10 and use Cells(counter + 1, 1), or loop the row variable from 2 to 11. The latter often reads more directly when the loop counter itself represents a worksheet row. Always check whether the target cells already contain data: this example overwrites their values.
Choosing the counter and loop limits
For Excel row numbers and ordinary counting, Long is usually a sensible counter type. VBA’s Integer type has a relatively small range, while worksheet row indexes can be much larger; using Long avoids that mismatch. Declare variables explicitly with Dim so the intended type is clear and typing mistakes are easier to catch.
Set the loop’s limits to match the task. A fixed limit such as 10 is appropriate for a known, fixed set. For a list that can grow, use a worksheet property such as the last used row in the relevant column, then loop through the actual data rows. Be careful in how you calculate that last row: a blank within the data or a formula returning an empty string may affect what counts as the last populated cell.
For a worksheet with headers in row 1 and records in rows 2 through 20, a loop should commonly start at 2 rather than 1. Otherwise, the macro may treat the heading as a record. Also consider what should happen when there are no records; a calculated last row may be above the first data row, so check the boundary before starting the loop.
Nested loops and early exits
You can place one For...Next loop inside another to work through two dimensions, such as rows and columns. Each loop needs its own counter, and each For must have a matching Next. For example, an outer row counter and an inner column counter can visit every cell in a rectangular range. Use distinct names such as rowNumber and columnNumber; reusing a counter makes the flow difficult to reason about and can produce incorrect results.
Exit For ends the innermost active For loop immediately. It is useful when a search finds what it needs and further passes are unnecessary. The procedure then continues at the statement after that loop’s Next. If loops are nested, exiting the inner loop does not automatically exit the outer loop, so add a condition or another control technique if the entire search should stop.
Use early exits deliberately. A loop that stops after finding the first match has different behavior from one that checks every item, especially if there may be duplicate matches. A short comment explaining the stopping condition can help a future reader understand why the loop does not reach its normal endpoint.
Common mistakes and troubleshooting
- Wrong direction: A descending loop needs a negative step and a higher starting value than ending value. Otherwise, it may execute zero times.
- Off-by-one limits: Remember that the endpoint is included when reached. Starting at row 2 and ending at row 10 covers nine rows.
- Changing the counter in the loop body: Avoid assigning a new value to the loop counter inside the body. It makes the progression harder to predict and can lead to skipped work or unexpected behavior.
- Incorrect worksheet reference: Unqualified references such as
Cells(counter, 1)use the active sheet. Qualifying them with a worksheet variable helps ensure the macro edits the intended sheet. - Counter overflow: A small numeric type may not hold the values needed for a large loop. Use
Longfor typical Excel row and iteration counts. - Missing or mismatched Next: Each loop must close with a matching
Next. In nested code, check that the loops close in reverse order from how they were opened.
If code stops unexpectedly, run it one statement at a time with the Visual Basic Editor’s debugging tools and inspect the counter value. A breakpoint on a line inside the loop can also help identify whether the loop is running too many times, too few times, or against an unexpected worksheet.
For…Next compared with other VBA loops
Use For...Next when the repetition has a numeric range or a known count. Use For Each...Next when you want to visit every object in a collection, such as each worksheet in a workbook or each cell in a selected range. That style avoids manually managing an index when the task is simply to process every member.
A Do While or Do Until loop fits a condition-controlled task where the number of repetitions is not known in advance. For example, code might continue until it finds a blank cell or until a condition becomes true. Ensure the condition can eventually change; otherwise, a condition-controlled loop can run indefinitely. Choosing the loop type around the task makes the stop rule easier to understand and maintain.
Run a macro safely in Excel
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module if needed, and place a procedure there. On Mac, the Visual Basic Editor is available through Excel’s Tools menu, and the keyboard shortcut can depend on the keyboard and function-key settings; use the menu if the shortcut does not open it. Excel for the web cannot create or run VBA macros.
Save a copy of the workbook before running a macro that changes cells. VBA actions cannot be undone with Ctrl+Z (or Command+Z on Mac), so a mistaken edit may require closing without saving or restoring the backup. Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code. Only enable macros from workbooks and sources you trust.
Frequently Asked Questions
Does a VBA For loop include the ending number? Yes. The loop includes the ending value if the counter reaches it through the chosen step. A loop from 1 to 5 with the default step runs five times.
How do I make a For loop count backward in VBA? Start at the larger number and add a negative step, such as For counter = 5 To 1 Step -1. The counter then decreases by one on each pass.
Can I omit the counter name after Next? Yes. Next alone is valid VBA. Writing Next counter is often clearer, particularly in nested loops, because it makes the matching loop variable easier to identify.
Why does my VBA For loop run zero times? The starting value and step may not move toward the ending value. For example, counting upward from 5 to 1 with the default positive step does not enter the loop; use a negative step to count down.
Which variable type should I use for a loop counter? Use Long for most Excel loops, especially when the counter may represent worksheet row numbers. It supports a much wider range than VBA’s Integer type.
Related
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Resize UserForm: Set Size and Enable ResizingTo resize an Excel VBA UserForm, set its Width and Height properties in the form’s Properties window or…
- VBA Dictionary Split: Turn Text into Keys and ValuesIn VBA, use the Split function to break a string into an array, then add the pieces to…
- Excel VBA: Find a Cell with Specific Text in a RangeTo find a cell containing specific text with Excel VBA, use the Range.Find method. Set options such as…
- Excel VBA: Find the Last Cell with Data in a ColumnIn Excel VBA, use Cells(Rows.Count, columnNumber).End(xlUp).Row to find the last nonblank cell’s row in a column. Check for…
- VBA Dictionary: How to Create and Use OneYes. In VBA, a dictionary is a key-value object that lets you store a value under a unique…
- VBA Loop Through a PivotTable: Examples and TipsTo loop through a PivotTable in Excel VBA, assign it to a PivotTable variable and use a For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.