To write a For loop in Excel VBA, open the Visual Basic Editor, insert a module, and place the work you want repeated between For counter = start To end and Next counter. For example, a loop can fill cells A1 through A5 with the numbers 1 through 5. The steps below show how to create, run, and adjust loops in desktop Excel for Microsoft 365 on Windows.
What You’ll Need
- A desktop copy of Excel with macros available. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros.
- A workbook to work in, plus permission to save it as an Excel Macro-Enabled Workbook (
.xlsm) if you need to keep the macro. - A basic idea of what should repeat, and the range of rows, cells, or items it should process.
Save a copy of your workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if the loop changes the wrong cells.
Step-by-Step
- Open the VBA editor. In Excel for Windows, press Alt+F11, or select Developer > Visual Basic. If Developer is not visible, open File > Options > Customize Ribbon and select the Developer check box. On a Mac, use Tools > Macro > Visual Basic Editor if that menu is available, or try Option+F11; some keyboards also require Fn. VBA macros cannot run in Excel for the web.
- Insert a standard module. In the Visual Basic Editor, find your workbook in the Project Explorer. If the pane is hidden, press Ctrl+R on Windows or use View > Project Explorer. Select the workbook project, then choose Insert > Module. A blank code window appears. A standard module is a convenient place for a general-purpose macro.
- Enter a complete For…Next procedure. The example below writes a sequence of numbers into the first five cells of column A on the currently active sheet. The variable
iis the counter: it takes each integer from 1 through 5, and the code inside the loop runs once for each value.
Sub FillNumbers()
Dim i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
' Write the loop counter into cells A1 through A5.
For i = 1 To 5
ws.Cells(i, 1).Value = i
Next i
End Sub
Type the procedure into the module, including Sub, End Sub, and the variable declarations. VBA does not require the indentation shown, but indenting the loop body makes it easier to see which statements repeat. This version targets whichever worksheet is active when you run it, so check the selected sheet first.
- Understand the loop parts. In
For i = 1 To 5,iis the counter, 1 is the starting value, and 5 is the ending value. VBA runs the body withiequal to 1, then 2, and so on through 5.Next iadvances the counter and returns control to the loop. The ending value is included: this example runs five times, not four. You can choose another counter name, but use the same name consistently in theForandNextstatements. - Run the macro and check the result. Return to Excel, select the sheet you want to use, and press Alt+F8 on Windows to open the Macro dialog. Select
FillNumbersand choose Run. You should see 1, 2, 3, 4, and 5 in cells A1:A5. You can also run the procedure from the editor by placing the cursor inside it and pressing F5. On a Mac, use Tools > Macro > Macros to choose a macro; keyboard shortcuts may depend on the keyboard and macOS settings. - Use a worksheet range as the loop limit. If you need to handle rows 2 through 20, set the counter limits to those row numbers and refer to each row using
Cells(i, column number). For example, the following checks column B and writes a status in column C. It assumes values to check are in B2:B20 on the active sheet.
Sub MarkLargeValues()
Dim i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
' Check each value in B2:B20 and write a result in column C.
For i = 2 To 20
If ws.Cells(i, 2).Value >= 100 Then
ws.Cells(i, 3).Value = "Review"
Else
ws.Cells(i, 3).Value = "OK"
End If
Next i
End Sub
Here, Cells(i, 2) means the cell in row i, column 2 (column B); Cells(i, 3) means column C. The loop processes 19 rows, including both row 2 and row 20. If all 19 values are numeric and at least 100, each corresponding cell in C receives Review; lower values receive OK. Adjust the row limits and column numbers to match your layout.
- Change the increment with Step. A standard loop increases by 1 each time. Add
Stepand a positive number to skip values. For instance,For i = 2 To 10 Step 2visits 2, 4, 6, 8, and 10. To count backwards, use a negative step and make the ending value smaller than the starting value, such asFor i = 10 To 1 Step -1. This can be useful when deleting rows, because deleting from the bottom upward helps avoid shifting rows that have not yet been processed. - Use For Each when you are looping through objects. If you want to process every worksheet rather than a numbered series, use a For Each loop. This example displays each sheet name in the Immediate Window, which you can show in the editor with View > Immediate Window.
Sub ListSheetNames()
Dim ws As Worksheet
' Visit each worksheet in the workbook that contains this macro.
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
Next ws
End Sub
Use For...Next when you need a counter or a known sequence of numbers. Use For Each when you want VBA to visit every item in a collection, such as each worksheet or each cell in a range. With a For Each loop, the variable must be an appropriate object type, such as Worksheet for worksheets.
Common Mistakes
- Forgetting Next. Every
Forneeds a matchingNext. If a loop is nested inside another loop, each one needs its ownNext, in reverse order from how the loops began. - Using the wrong limits. The end value is included. A loop from 2 to 20 handles 19 rows, while a loop from 2 to 19 handles 18. Confirm whether your data includes a header row before choosing the starting row.
- Using an unsuitable data type. A
Longvariable is a safe default for row counters. AnIntegertops out at 32,767, which is too small for many Excel row numbers. - Mixing up row and column numbers. In
Cells(row, column), the row comes first. Column A is 1, B is 2, and C is 3. A reversed pair can write to an unexpected cell. - Running on the wrong sheet. A macro that uses
ActiveSheetaffects the sheet selected at run time. Select the intended sheet first, or explicitly set a worksheet variable to a sheet in your workbook. - Changing a counter inside the loop. Let the For statement manage its counter. Manually changing the counter in the loop body can cause skipped iterations or confusing results.
Tips
- Use
Option Explicitat the top of a module to require variable declarations. In the VBA editor, Tools > Options includes a Require Variable Declaration setting for new modules. Declaring variables helps catch misspelled names before they cause harder-to-find errors. - Give counters and worksheet variables meaningful names when a procedure becomes more involved. Names such as
rowNumortargetSheetcan be easier to follow than short names in longer code. - Test a loop on a small range first. Once the results are right, expand the start and end values. This is particularly helpful before a loop edits, clears, or deletes workbook content.
- For a changing list, you can calculate the last used row before looping rather than hard-coding it. Choose the column that reliably contains data, and account for blank cells or other layout quirks before relying on that value.
- Save a workbook containing VBA as
.xlsm. Saving as a standard.xlsxfile does not preserve VBA macros. Only enable macros in files you trust.
Troubleshooting
The macro does not appear in the Macro dialog: Check that the procedure is in a standard module and has a name such as Sub FillNumbers() with no required arguments. A procedure marked Private or placed in a worksheet code module may not appear in the same way as a public macro in a standard module. Also check that the workbook containing it is open.
Excel says macros are disabled: Confirm the file is from a trusted source and that it is saved as .xlsm. Follow your organization’s security policy; do not lower macro security globally just to run an unknown file. Excel for the web does not run VBA, so open the workbook in the desktop app.
The loop runs but writes to unexpected cells: Check the active worksheet, row and column arguments, and the start and end values. In the example, Cells(i, 1) writes to column A, and i is the row number. If the macro should target one particular sheet, set the worksheet variable explicitly rather than relying on ActiveSheet.
The code stops with a compile error: Look for a missing Next, an unmatched If/End If, a misspelled variable, or quotation marks that were copied as typographic “smart quotes.” VBA needs ordinary straight quotation marks in text values. The editor usually highlights the line where it detected a problem, though the actual cause can be on the preceding line.
The loop seems to run forever or Excel stops responding: Check that the counter is not being reset or changed unexpectedly and that the end value and Step direction make sense. For example, a downward loop needs a negative Step. Press Esc or Ctrl+Break on Windows to try to interrupt a running macro; if Excel does not respond, wait briefly before attempting to close it, and restore the backup if needed.
Frequently Asked Questions
Does a For loop include its ending number?
Yes. A loop written from 1 to 5 runs with counter values 1, 2, 3, 4, and 5. Plan your row limits with that inclusive endpoint in mind.
Can a For loop start at zero or another number?
Yes. Choose any appropriate start and end values, such as 0 to 4 or 2 to 20. The counter does not have to match a worksheet row, as long as your code maps it to the right cells.
How do I make a For loop count by two?
Add Step 2 after the end value, as in For i = 2 To 10 Step 2. That sequence visits 2, 4, 6, 8, and 10.
What is the difference between For…Next and For Each?For...Next is suited to a counter that moves through a numeric range. For Each visits each object in a collection, such as worksheets or cells in a range, without needing to set a numeric counter.
Can I stop a For loop early?
Yes. Use Exit For inside a condition when the loop should stop before reaching its normal endpoint. Keep the condition specific, and make sure leaving early is appropriate for the task.
Can I use VBA loops in Excel for Mac?
Excel for Mac supports VBA, though editor access and keyboard shortcuts can differ from Windows. Excel for the web does not run VBA macros. Save a macro-enabled workbook as .xlsm when you need to retain its code.
Related
- Flash Fill in Excel: How to Fill Data by Pattern Correctly
- Flash Fill in Excel: Fill to the Last Row
- Flash Fill in Excel: How It Works and When to Use It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary: Search by Key and Check Whether It ExistsTo search a VBA Dictionary by key, call its Exists method before retrieving the value: use If dict.Exists(searchKey)…
- Excel VBA: Protect a Sheet and Manage Permissions SafelyYes. In Excel VBA, protect a worksheet with its Protect method, for example Worksheets("Entry").Protect Password:="demo-pass". Add options such…
- VBA Loops Within Loops: How Nested Loops Work in ExcelYes. VBA can run one loop inside another; this is called a nested loop. The outer loop runs…
- Excel VBA: How to Loop Through an Array of StringsIn Excel VBA, use For Each to visit every string in an array, or use a counter with…
- Autofit VBA Range: Columns, Rows, and ExamplesIn Excel VBA, use the AutoFit method to size worksheet columns or rows to fit their contents. For…
- Excel VBA: Find Unique Entries in a ColumnTo find distinct entries in an Excel column with VBA, loop through the data, add each value only…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.