Practice and Answer Key: A Macro that Tags Late Orders

Suppose Raja wants a button that marks late orders (more than 15 minutes) in column J of the Orders sheet and shows how many there are. Use the Module 3 mini dataset in A1:I11. All data is fictional.

Use the Orders sheet: Order ID in column A, Mins in column H, ten data rows (2 to 11).

Exercise (one under the other):

1. Press Alt + F11, click Insert › Module.
2. Write a Sub named TagLateOrders.
3. Find the last row from column A.
4. Loop from row 2 to the last row. If column H is more than 15, write "Late" in column J, else clear J.
5. Count the late rows and show the count in a MsgBox.
6. Save as .xlsm and add a button with Developer › Insert › Button.

Expected result (check your numbers):

All other rows stay empty in column J. The MsgBox says: Late orders: 3. Check with =COUNTIF(H2:H11,">15").

Sub TagLateOrders()
    Dim ws As Worksheet, r As Long, lastRow As Long, n As Long
    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For r = 2 To lastRow
        If ws.Cells(r, "H").Value > 15 Then
            ws.Cells(r, "J").Value = "Late"
            n = n + 1
        Else
            ws.Cells(r, "J").ClearContents
        End If
    Next r
    MsgBox "Late orders: " & n
End Sub

Common mistakes:

Ravindra Bagale's rule: test a macro on a copy of the file first, and step through it once with F8.