Decisions in VBA: If…Then, And/Or and Select Case

📎 This article includes 1 downloadable practice file ↓

A macro that does the same thing to every row is useful. A macro that looks at each row and decides what to do is far more useful — flag late payments, pick the right GST slab, skip blank rows. This lesson covers the decision tools VBA gives you.

If…Then

If amount > 100000 Then
    ws.Cells(r, "F").Value = "Needs approval"
End If

If amount > 100000 Then ws.Cells(r, "F").Value = "Needs approval"   ' one-line version

If…Else and ElseIf

If score >= 90 Then
    grade = "A"
ElseIf score >= 75 Then
    grade = "B"
ElseIf score >= 60 Then
    grade = "C"
Else
    grade = "Below 60"
End If

VBA checks the conditions top to bottom and stops at the first true one — so put the strictest test first. If you wrote score >= 60 first, a 95 would get “C”.

And, Or, Not

If region = "North" And amount > 50000 Then ...
If status = "Overdue" Or daysLate > 30 Then ...
If Not IsEmpty(ws.Cells(r, 1)) Then ...

Checking cells safely

Select Case: cleaner than long If chains

Select Case ws.Cells(r, "C").Value
    Case "North", "South"
        zone = "Zone 1"
    Case "East"
        zone = "Zone 2"
    Case Else
        zone = "Other"
End Select

Select Case amount
    Case Is < 0:            band = "Refund"
    Case 0 To 49999:        band = "Small"
    Case 50000 To 199999:   band = "Medium"
    Case Is >= 200000:      band = "Large"
End Select

Ranges with To, lists with commas and comparisons with Is make Select Case ideal for slabs and categories.

Real example: GST slab by product category

Function GstRate(ByVal category As String) As Double
    Select Case LCase$(Trim$(category))
        Case "books", "fresh food":         GstRate = 0
        Case "packaged food", "footwear":   GstRate = 0.05
        Case "mobiles", "furniture":        GstRate = 0.12
        Case "electronics", "cosmetics":    GstRate = 0.18
        Case Else:                          GstRate = 0.18
    End Select
End Function

The rates here are examples for practice — always use the current official rate list in real work.

Real example: flag overdue invoices

Sub FlagOverdue()
    Dim ws As Worksheet, r As Long, due As Variant
    Set ws = ThisWorkbook.Worksheets("Invoices")
    For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        due = ws.Cells(r, "D").Value
        If ws.Cells(r, "F").Value = "Paid" Then
            ws.Cells(r, "G").Value = ""
        ElseIf IsDate(due) Then
            If due < Date Then ws.Cells(r, "G").Value = "Overdue " & (Date - due) & " days"
        Else
            ws.Cells(r, "G").Value = "Check due date"
        End If
    Next r
End Sub

Where people go wrong

• Text comparisons fail — “North ” with a space or “north” in lower case. Use Trim$ and LCase$, or put Option Compare Text at the top of the module.
• Type mismatch on comparisons — the cell holds an error like #N/A. Test IsError first.
• Missing End If — every multi-line If needs one. Indent consistently and it’s easy to spot.

Practice

The workbook has marks to grade, invoices to flag, products to put into GST slabs and a few rows of messy data (spaces, errors, text numbers) to make your conditions robust.

Next: five handy macros you’ll reuse constantly.