Day 5 — For loops - doing it to every row

This is the day VBA starts earning its keep. One loop replaces an afternoon of dragging formulas.

For ... Next

For counter = start To finish
do this, over and over
Next counter

Sub CountToFive()
    Dim i As Long

    For i = 1 To 5
        Debug.Print "Pass number", i
    Next i
End Sub

The counter starts at 1, the block runs, Next adds 1, and it goes round again until the counter passes 5.

The one that matters: looping down rows

Here is the whole point of Day 3. Because Cells takes a number, the loop counter can be the row number.

Sub FillTotals()
    Dim i As Long

    Range("H1").Value = "Total"

    For i = 2 To 17
        Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
    Next i
End Sub

Four lines, sixteen rows filled. Look at column H on the sheet below - every cell outlined in gold was written by that loop. Change 17 to 10 and run it again to watch the loop stop early.

Running totals

Declare the total before the loop, add to it inside, print it after. Getting that order wrong is the most common loop bug there is.

Sub SumUnits()
    Dim i As Long
    Dim total As Long

    total = 0

    For i = 2 To 17
        total = total + Cells(i, 6).Value
    Next i

    Debug.Print "Total units:", total
End Sub

Put total = 0 inside the loop and you get nonsense It resets on every pass, so at the end it holds only the last row instead of the sum. If a running total ever comes out equal to the final row, this is what happened.

Counting and conditions together

A loop with an If inside is the workhorse of MIS reporting. Count them, sum them, flag them.

Sub NorthReport()
    Dim i As Long
    Dim orders As Long
    Dim value As Double

    For i = 2 To 17
        If Cells(i, 3).Value = "North" Then
            orders = orders + 1
            value = value + Cells(i, 6).Value * Cells(i, 7).Value
        End If
    Next i

    Debug.Print "North orders:", orders
    Debug.Print "North value:", value
End Sub

Step - counting by something other than 1

Sub Stepping()
    Dim i As Long

    For i = 2 To 17 Step 5
        Debug.Print "Row", i, Cells(i, 4).Value
    Next i

    For i = 5 To 1 Step -1
        Debug.Print "Counting down", i
    Next i
End Sub

Step -1 is not a party trick When you delete rows in a loop you must go bottom to top. Delete row 5 going downwards and every row below shifts up, so the loop skips one. Going upwards, deleting a row never moves anything you have not visited yet.

Exit For - stop early

Sub FindFirstLaptop()
    Dim i As Long

    For i = 2 To 17
        If Cells(i, 5).Value = "Laptop" Then
            Debug.Print "First laptop is on row", i
            Exit For
        End If
    Next i
End Sub

Without Exit For that loop would keep checking all sixteen rows after it already had the answer. On sixteen rows nobody notices. On 200,000 rows you very much do.

Nested loops

A loop inside a loop. The inner one runs completely for every single pass of the outer one.

Sub RowsAndColumns()
    Dim r As Long
    Dim c As Long

    For r = 2 To 4
        For c = 3 To 5
            Debug.Print "Row " & r & " Col " & c & " = " & Cells(r, c).Value
        Next c
    Next r
End Sub

Three rows times three columns is nine lines of output. Watch the pattern: the column number cycles fastest.

Try these yourself

Loop rows 2 to 17 and print only the orders where units are 10 or more.
Fill column H with the order total, but write the word "Bulk" instead when units are over 10.
Count how many orders Rahul has, using a loop and an If.
Total the value of every Laptop order. Check it against 156000 + 104000 + 52000 + 208000.
Explain why a loop that deletes rows must run backwards.

← PreviousDay 4 — If, ElseIf and Select Case Next →Day 6 — The last row, Do loops and For Each