How do I match a text pattern such as a wildcard in Select Case?

The Like operator cannot appear after Case on its own, so use Select Case True and write Case customerName Like Sup followed by an asterisk on each line. Like supports the wildcards asterisk and question mark, the hash sign for a single digit, and character lists in square brackets.

VBA Select Case Statement: Ranges, Lists, Case Else

Part of the free Module 12: Excel VBA Course · Lesson 7 of 18 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

The VBA Select Case statement tests one expression against a list of possible values and runs the block belonging to the first match, then jumps to End Select. It is the readable replacement for a long If ElseIf ladder, and a Case line can hold exact values, comma separated lists, To ranges, Is comparisons or a Case Else catch all.

What the Select Case statement does

Select Case is the VBA version of the switch statement found in other languages. You give it one test expression: a variable, a cell value, a function result or the literal True. VBA evaluates that expression once, then walks down the Case lines in order. The first Case whose pattern matches runs its block of code, and control moves straight to End Select. No other Case runs, and there is no fall through to the next block.

Reach for Select Case whenever the same value is being compared against three or more possibilities: grading scores, mapping a department code to a department name, turning a month number into a quarter, or routing a menu choice. For one or two conditions the If Then Else statement is still the natural choice.

Select Case syntax

The skeleton is short. Note that Option Explicit belongs on the very first line of the module, above every procedure, and forces you to declare each variable. It is shown once here and assumed in every later example on this page.

Option Explicit

Sub Select_Case_Skeleton()
    Dim testValue As Variant

    testValue = 7

    Select Case testValue
        Case 1
            MsgBox "One"
        Case 2, 3
            MsgBox "Two or three"
        Case 4 To 9
            MsgBox "Between four and nine"
        Case Else
            MsgBox "Something else"
    End Select
End Sub

Everything after the Case keyword is a pattern. The table below lists every form a pattern can take, so you can pick the shortest one that expresses your rule.

Case form	Meaning	Example
Single value	Matches one exact value	`Case 10` or `Case "North"`
Comma list	Matches any value in the list	`Case 6, 7, 8`
To range	Inclusive range, smaller value first	`Case 1 To 5`
Is comparison	Comparison operator against the test value	`Case Is >= 90`
Mixed list	Several forms on one line, separated by commas	`Case 1, 5 To 9, Is > 100`
Case Else	Runs when nothing above matched	`Case Else`

Exact values and comma lists

The simplest use is a lookup: one input, several known answers. The macro below converts a short colour code into a full colour name and warns the user when the code is not recognised.

Sub Check_Selected_Colour()
    Dim selectedColour As String

    selectedColour = "R"

    Select Case selectedColour
        Case "R"
            MsgBox "Red"
        Case "G", "L"
            MsgBox "Green"
        Case "B"
            MsgBox "Blue"
        Case Else
            MsgBox "Unknown colour code: " & selectedColour
    End Select
End Sub

The second Case line shows a comma list: both G and L map to Green without repeating the MsgBox line. Read the list as a set of alternatives joined by OR.

Ranges with To and comparisons with Is

To builds an inclusive range and Is lets you use a comparison operator such as >, >=, <, <= or <>. Both can appear on the same line as ordinary values.

Sub Check_Numbers()
    Dim myNumber As Long

    myNumber = 8

    Select Case myNumber
        Case Is < 0
            MsgBox "Negative"
        Case 0
            MsgBox "Zero"
        Case 1 To 5
            MsgBox "Between 1 and 5"
        Case 6, 7, 8
            MsgBox "Between 6 and 8"
        Case 9 To 10
            MsgBox "Nine or ten"
        Case Is > 10
            MsgBox "Greater than 10"
    End Select
End Sub

The value 8 fails the first three tests, matches the comma list on the fourth Case and displays Between 6 and 8. The remaining Case lines are never evaluated, which is part of why Select Case is quick.

Ranges must be written with the smaller value first. Case 10 To 1 compiles without complaint but never matches anything, and that silent failure is one of the most common bugs in this statement.

Matching text and case sensitivity

Text works exactly like numbers, and To ranges compare alphabetically, so Case "A" To "M" matches every surname in the first half of the alphabet. By default VBA compares text in binary mode, which means the comparison is case sensitive and “north” does not match “North”. You have two clean ways to deal with that.

Sub Route_By_Region()
    Dim regionName As String

    regionName = ThisWorkbook.Sheets("Data").Range("B2").Value

    Select Case UCase(Trim(regionName))
        Case "NORTH", "NORTH EAST"
            MsgBox "Northern team"
        Case "SOUTH"
            MsgBox "Southern team"
        Case ""
            MsgBox "Region is blank"
        Case Else
            MsgBox "No team mapped for " & regionName
    End Select
End Sub

Wrapping the test expression in UCase and Trim normalises the input, so stray spaces and lower case letters still match. The alternative is to put Option Compare Text at the top of the module, which makes every string comparison in that module case insensitive.

Select Case True for unrelated conditions

A Case line cannot hold the Like operator or a full Boolean expression on its own. When your rules involve more than one variable, test the literal True instead and write a complete condition on each Case line. The first condition that evaluates to True wins.

Sub Calculate_Commission()
    Dim sales As Double
    Dim region As String
    Dim rate As Double

    sales = 1500
    region = "North"

    Select Case True
        Case sales >= 1000 And region = "North"
            rate = 0.1
        Case sales >= 1000
            rate = 0.08
        Case sales >= 500
            rate = 0.05
        Case Else
            rate = 0
    End Select

    MsgBox "Commission rate: " & Format(rate, "0%")
End Sub

Order matters here. The most specific rule sits first so that a large northern sale earns 10 percent rather than falling into the broader 8 percent band. The same pattern lets you use wildcards, for example Case supplierName Like "Sup*".

Nesting Select Case

A Select Case block can contain another one. Nesting is the honest way to express a two level decision such as category first, then size within that category.

Sub Set_Delivery_Charge()
    Dim productType As String
    Dim weightKg As Double
    Dim charge As Double

    productType = "Fragile"
    weightKg = 12

    Select Case productType
        Case "Fragile"
            Select Case weightKg
                Case Is <= 5
                    charge = 120
                Case 5.01 To 20
                    charge = 260
                Case Else
                    charge = 480
            End Select
        Case "Standard"
            Select Case weightKg
                Case Is <= 5
                    charge = 60
                Case Else
                    charge = 150
            End Select
        Case Else
            charge = 0
    End Select

    MsgBox "Delivery charge: " & charge
End Sub

Indent each level and give every inner block its own End Select. Two levels are readable; beyond that, move the inner logic into a separate procedure.

Select Case versus If ElseIf

Both statements do conditional branching, and neither is always right. The table sums up the choice.

Situation	Better choice	Why
One or two simple tests	If Then Else	Shorter, and a single line If reads well
Three or more values of the same variable	Select Case	The variable is named once, so typing errors drop
Score bands or code lookups	Select Case	To ranges and comma lists express bands directly
Conditions on several different variables	Either	If ElseIf, or Select Case True with full expressions
Inside a loop over thousands of rows	Select Case	The test expression is evaluated once per pass, not once per branch

Worked example: grade a column of scores

Put this small table on a sheet named Data, with headings in row 1 and the scores in column B.

A: Student	B: Score	C: Grade (written by the macro)
Amit	92	A
Bela	78	B
Chris	64	C
Dev	41	Fail
Eva	(blank)	No score

Now run the macro. It loops from row 2 to the last used row, tests each score and writes the grade next to it.

Sub Grade_Scores()
    Dim sh As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set sh = ThisWorkbook.Worksheets("Data")
    lastRow = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row

    For i = 2 To lastRow
        Select Case sh.Cells(i, "B").Value
            Case ""
                sh.Cells(i, "C").Value = "No score"
            Case Is >= 90
                sh.Cells(i, "C").Value = "A"
            Case 75 To 89.99
                sh.Cells(i, "C").Value = "B"
            Case 60 To 74.99
                sh.Cells(i, "C").Value = "C"
            Case 0 To 59.99
                sh.Cells(i, "C").Value = "Fail"
            Case Else
                sh.Cells(i, "C").Value = "Check value"
        End Select
    Next i

    MsgBox "Graded " & lastRow - 1 & " rows."
End Sub

Column C now reads A, B, C, Fail and No score. Note the order: the blank test comes first because an empty cell would otherwise be treated as zero and graded Fail, and Case Else catches text or negative numbers instead of letting them pass unnoticed.

Tips and common mistakes

Order Case lines from most specific to least specific. Only the first match runs, so a broad Case Is > 0 placed early swallows every band below it.
Always add Case Else. Without it an unexpected value does nothing at all, which is far harder to spot than a message box saying the value was not recognised.
Write ranges smaller value first. Case 10 To 1 never matches, and VBA gives no warning.
Mind the gaps between bands. Case 1 To 5 followed by Case 7 To 10 leaves 6 unhandled; use touching bands or Is comparisons.
Remember text is case sensitive by default. Normalise with UCase, or put Option Compare Text at the top of the module.
There is no fall through and no Break keyword. Unlike C or JavaScript, VBA runs at most one Case block, so nothing needs to be broken out of.
Test the cell value, not the cell. Use Range("B2").Value so the comparison works on the underlying number rather than the formatted display.

Errors and how to fix them

Symptom	Cause	Fix
Compile error: Case without Select Case	A Case line sits outside a block, or End Select is missing from a nested block	Give every Select Case its own End Select and re-indent the procedure
Compile error: Expected expression	A comparison written as `Case > 10`	Add the keyword: `Case Is > 10`
Nothing happens for valid input	Ranges reversed, or a gap between bands	Check the To order and add a Case Else that reports the value
Run time error 13, type mismatch	A cell holds text or an error value while the Case lines expect numbers	Guard with `If IsNumeric(cellValue) Then` before the Select Case
Blank cells graded as zero	An empty cell converts to 0 in a numeric comparison	Put `Case ""` as the first Case line
Text match fails on correct data	Binary comparison plus stray spaces	Test `UCase(Trim(value))` and use upper case Case labels

Practice exercise

Write a macro that reads a month number from a cell and shows the quarter using Case 1 To 3, Case 4 To 6, Case 7 To 9 and Case 10 To 12, with Case Else warning about an invalid month.
Convert a department code in column A into a department name in column B for 20 rows, using a comma list so that both HR and PER map to Human Resources.
Rewrite one of your existing If ElseIf macros as a Select Case block and compare the line counts.
Use Select Case True to set a discount rate from two variables: order value and customer type.
Add a nested Select Case to the grading macro so that an A grade is further split into A and A plus at 97 and above.

Key takeaways

Select Case evaluates one expression once and runs the first matching Case block only.
Case lines accept exact values, comma lists, To ranges, Is comparisons and any mix of them.
Case Else is your safety net and should be present in almost every block.
Select Case True turns the statement into a rule engine for conditions on several variables.
Text comparison is case sensitive unless you use UCase or Option Compare Text.
Prefer Select Case over If ElseIf once you are testing the same variable three or more times.

Related lessons

Excel VBA course hub
If Then Else and ElseIf in VBA
Loops in VBA
Variables and data types in VBA
MsgBox and InputBox
VLOOKUP on the worksheet, the formula alternative to a code lookup
Conditional formatting for banding values without any code
Select Case statement reference on Microsoft Learn

Frequently asked questions

Is Select Case faster than If ElseIf in VBA?

Marginally, because the test expression is evaluated once rather than on every ElseIf line, and VBA stops at the first match. Inside a loop over tens of thousands of rows that adds up, but for ordinary macros the real gain is readability: the variable is named once, so there is less to mistype.

Can a Select Case statement test more than one variable?

Not directly, because it takes a single test expression. Use Select Case True and write a complete Boolean condition on each Case line, for example Case sales >= 1000 And region = “North”. Alternatively nest a second Select Case inside the block of the first.

How do I match a text pattern such as a wildcard?

The Like operator cannot appear after Case on its own, so use Select Case True and write Case customerName Like “Sup*” on each line. Like supports the wildcards asterisk and question mark, the hash sign for a single digit, and character lists in square brackets.

Why does my Case range never match?

Almost always because the range is written the wrong way round. A To range must have the smaller value first, so Case 10 To 1 matches nothing and VBA raises no error. Check also that the cell really holds a number, since a numeric string can fail a numeric Case.

Does Case Else have to be last?

Yes. Case Else must be the final Case line in the block, just before End Select, and there can be only one of them. It runs when no earlier Case matched, which makes it the ideal place to report an unexpected value with MsgBox or write it to a log.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.