Project: A Report Macro — Excel lesson

Your manager has handed you a macro that builds a weekly sales report. Read it line by line, then check yourself: use formulas to get exactly what it will write to the sheet, and answer questions about the code.

Option Explicit

Sub SalesReport()
    Dim ws As Worksheet
    Dim r As Long, lastRow As Long, bigCount As Long
    Dim total As Double

    Set ws = Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Application.ScreenUpdating = False

    For r = 2 To lastRow
        ws.Cells(r, 4).Value = ws.Cells(r, 2).Value * ws.Cells(r, 3).Value
        If ws.Cells(r, 4).Value >= 10000 Then
            ws.Cells(r, 5).Value = "Large"
            bigCount = bigCount + 1
        Else
            ws.Cells(r, 5).Value = "Regular"
        End If
        total = total + ws.Cells(r, 4).Value
    Next r

    ws.Range("H1").Value = total
    ws.Range("H2").Value = bigCount
    ws.Range("H3").Value = Application.WorksheetFunction.Max(ws.Range("D2:D" & lastRow))

    Application.ScreenUpdating = True
    MsgBox "Report ready: " & lastRow - 1 & " rows"
End Sub

Reading tips: column 2 is B (price), 3 is C (quantity), 4 is D (total), and 5 is E (category). "D2:D" & lastRow joins an address: when lastRow = 9 you get D2:D9. The >= comparison includes the boundary: exactly 10,000 counts as "Large".

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.

7. Project: A Report Macro

Test yourself

Practice is part of Pro