If, ElseIf and Else

The decision statement

If runs a block of code only when a condition is true. The condition is anything that comes out True or False, the block is indented by four spaces, and it always finishes with End If. Else catches everything the condition missed; ElseIf adds further tests in between. VBA works down the chain and runs the first branch that is true, then skips the rest.

Sub CheckValue()
    Dim amount As Double
    amount = Range("B2").Value

    If amount > 1000 Then
        Range("C2").Value = "Large order"
    End If
End Sub

A macro that classifies real-looking data

Paste this into a module (Alt+F11 → Insert → Module) and press F5. It creates its own sheet with fourteen orders, and it deliberately leaves some amounts blank and breaks one with #N/A — because that is what an export from someone else's system looks like.

Option Explicit

Sub ClassifyOrders()
    Dim ws As Worksheet
    Dim cell As Range
    Dim dataRows As Long
    Dim lastRow As Long
    Dim i As Long
    Dim amount As Double
    Dim label As String
    Dim problems As Long

    dataRows = 14

    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Orders " & Format(Now, "hh-mm-ss")

    ' --- sample data: a few blanks and one broken lookup, on purpose ---
    ws.Range("A1:C1").Value = Array("Order", "Amount", "Class")

    For i = 1 To dataRows
        ws.Cells(i + 1, 1).Value = "ORD-" & Format(i, "000")
        If i = 7 Then
            ws.Cells(i + 1, 2).Formula = "=NA()"
        ElseIf i Mod 5 <> 0 Then
            ws.Cells(i + 1, 2).Value = 900 * i - 1500 * (i Mod 3)
        End If
    Next i

    lastRow = dataRows + 1

    ' --- the decisions ---
    For i = 2 To lastRow
        Set cell = ws.Cells(i, 2)

        If IsError(cell.Value) Then
            label = "Check the source"
            problems = problems + 1
        ElseIf IsEmpty(cell.Value) Then
            label = "Missing amount"
            problems = problems + 1
        Else
            amount = cell.Value
            If amount > 8000 Then
                label = "Key account"
            ElseIf amount > 3000 Then
                label = "Large"
            ElseIf amount > 0 Then
                label = "Standard"
            Else
                label = "Zero or negative"
            End If
        End If

        ws.Cells(i, 3).Value = label
    Next i

    ws.Range("A1:C1").Font.Bold = True
    ws.Range("B2:B" & lastRow).NumberFormat = "#,##0.00"
    ws.Columns("A:C").AutoFit

    MsgBox "Classified " & dataRows & " orders, " & problems & _
        " of them flagged for a human.", vbInformation, "Done"
End Sub

Line by line

• Set cell = ws.Cells(i, 2) — one reference, used three times below. Cheaper to read, and cheaper to change when the amount moves to column D.
• If IsError(…) Then comes first, before anything touches the value as a number. This is the order that keeps the macro alive on messy data.
• ElseIf IsEmpty(…) — a blank cell is not a zero, and pretending otherwise is how blanks end up in the wrong category.
• amount = cell.Value — only reached once the value is known to be usable. From here on the tests are plain arithmetic.
• The inner chain runs from the largest threshold downwards. Put amount > 0 at the top instead and everything on earth is Standard, because the first true branch wins and nothing below it is even looked at.
• problems = problems + 1 — a counter, so the message box at the end can tell you how much of the file needs a human.
• The nested If sits inside Else and is indented one level further. That indentation is what tells you which End If belongs to which If.

Change it and run again

Move the thresholds, or add an ElseIf amount > 20000 Then label = "Strategic" above the others and watch how many rows change category. Then delete the IsError branch and run it once more — which brings us to the trap.

The trap: empty cells and error values are not numbers

An empty cell handed to a numeric comparison behaves as zero, silently. A cell holding #N/A or #REF! is worse: comparing it stops the macro with run-time error 13, type mismatch, on a line that is obviously correct — and the row it died on is nowhere in the message.

' Looks harmless. Both lines are wrong.
If ws.Range("B6").Value < 3000 Then label = "Small"    ' B6 is EMPTY -> counts as 0 -> "Small"
If ws.Range("B8").Value > 0 Then label = "Positive"    ' B8 is #N/A  -> run-time error 13

' The order that survives real data:
If IsError(cell.Value) Then
    ' #N/A, #REF!, #DIV/0! ... a human has to look
ElseIf IsEmpty(cell.Value) Then
    ' truly blank, which is not the same as zero
ElseIf Not IsNumeric(cell.Value) Then
    ' text where a number was expected
Else
    ' now, and only now, compare numbers
End If

Why that costs an hour

Both failures point at the wrong place. The blank-as-zero one produces a report that is merely wrong, so you look for the bug in your thresholds. The #N/A one crashes on a comparison you have read fifteen times. Test what a value is before you test what it says: IsError, then IsEmpty, then IsNumeric, then the arithmetic.

How it goes on

This macro used a For loop to walk the rows without explaining it — that is the subject of lesson four, along with For Each and the Do loops for when you cannot count the repetitions in advance. Before that, the next two lessons go deeper into the conditions themselves: And, Or, Not, the fact that VBA evaluates both sides of an And even when the first is already false, and Select Case for when a chain of four ElseIfs stops being readable.