3. VBA: Looping Over a Range

Three loops handle most tasks:

• For r = 2 To lastRow … Next r goes by row numbers, when you need to reach different columns of the same row through Cells(r, n). Step 2 steps by 2, and Step -1 runs backward;
• For Each cell In Range("B2:B100") … Next cell goes through each cell of a range;
• Do While condition … Loop repeats while the condition is true, for example while a cell is not empty. Don't forget to increase the counter, or the loop never ends.

The last filled row: lastRow = Cells(Rows.Count, 1).End(xlUp).Row. It's like pressing Ctrl+↑ from the very bottom of column A. Exit For leaves the loop early. If the loop deletes rows, go from the bottom up (Step -1), or the rows will shift and some will be skipped.

Sub InvoiceStatus()
    Dim r As Long, lastRow As Long, cnt As Long
    Dim total As Double
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 2 To lastRow
        If Cells(r, 3).Value < Range("F1").Value Then
            Cells(r, 4).Value = "Overdue"
            cnt = cnt + 1
            total = total + Cells(r, 2).Value
        Else
            Cells(r, 4).Value = "On time"
        End If
    Next r
    Range("F2").Value = cnt
    Range("F3").Value = total
End Sub

An invoice is overdue if its due date (column C) is earlier than the report date in F1. Reproduce the macro's result with formulas. A good habit: if one formula solves the task, you don't need a macro. A macro pays off when the action repeats every day on new files.

Speed: reading cells one at a time is slow. For thousands of rows, load the range into an array (arr = Range("A2:C5000").Value), process it in memory, and write it back in one go. While the macro runs, turn off screen redrawing: Application.ScreenUpdating = False.

The simulator doesn't run macros or scripts. Run the code from the lesson in desktop Excel (VBA) or in Excel for the web (Office Scripts). Here you learn to read code and check yourself with formulas and a quiz.