In Excel: Select Case expression, then one Case line per group of values, an optional Case Else, and End Select to close the block.
VBA macro: Select Case in VBA
Sub CategoriseByCity()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
Select Case LCase(Trim(CStr(ws.Cells(i, 1).Value)))
Case "berlin", "munich", "hamburg"
ws.Cells(i, 2).Value = "DE"
Case "paris", "lyon"
ws.Cells(i, 2).Value = "FR"
Case ""
ws.Cells(i, 2).Value = "(missing)"
Case Else
ws.Cells(i, 2).Value = "Other"
End Select
Next i
End SubThe test expression is evaluated once, at the top, which is why wrapping it in LCase and Trim there normalises every comparison below for free. A single Case can list several values separated by commas, and Case Else catches everything unmatched.
How to run this macro
- Press Alt + F11 to open the VBA editor.
- Insert > Module.
- Paste the code above.
- Press
F5, or close the editor and run it from Developer > Macros. - Save the file as .xlsm so the macro is kept.
Write Select Case followed by the expression to test, applying any normalisation such as LCase or Trim right there.
Add one Case line per group, listing multiple values with commas, ranges with To, or comparisons with Is.
Put the most specific cases first — matching stops at the first hit.
Add Case Else as the final branch to catch everything unmatched.
Close the block with End Select and test with a value that falls into Case Else.
What this does
Select Case is VBA's answer to a long chain of ElseIf tests against the same value, and it is what other languages call a switch — there is no Switch statement in VBA, though there is an unrelated Switch() function that returns a value rather than branching. The expression at the top is evaluated exactly once, then compared against each Case in order until one matches; the rest are skipped and execution resumes after End Select. Case lines are more expressive than a plain equality test: they take comma-separated lists (Case 1, 3, 5), ranges (Case 10 To 20), and comparisons via the Is keyword (Case Is >= 100). Because the expression is evaluated once, any normalisation belongs there — LCase and Trim around the test expression apply to every branch below, which is both shorter and less error-prone than repeating them. Unlike C-style switches there is no fall-through and no Break: exactly one branch runs. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel vba switch case”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Column A holds city names typed by different people, with inconsistent casing and stray spaces, and column B should hold a country code. Sub CategoriseByCity() normalises once in the Select Case line and then matches groups of cities per branch. The Case "" branch catches blanks before Case Else does, which is the ordering that matters — Case Else is a catch-all and anything you want handled specifically must appear above it. Numeric work uses the other two forms: Select Case score with Case Is >= 90, Case 80 To 89, Case Else reads far more cleanly than the equivalent ElseIf chain, and the To range form is inclusive at both ends. Rewriting that same logic as ElseIf would repeat the variable name on every line, which is exactly where copy-paste errors creep in. Select Case is the readable form of "this value could be one of several things". It states the tested expression once instead of on every line, supports lists and ranges directly, and makes the catch-all explicit — all of which matter most in classification code that someone else will need to extend later. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. Here is the takeaway for “excel vba switch case”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.
Common mistakes
- Writing Case x >= 100 instead of Case Is >= 100. Without the Is keyword VBA compares the test expression against the result of the comparison, which is a Boolean, and the branch quietly never matches.
- Expecting fall-through from one Case into the next. VBA runs exactly one branch and jumps to End Select; there is no Break because none is needed, and code written expecting C semantics silently drops steps.
- Omitting Case Else. Unmatched values then pass through the block leaving the target cell untouched, which looks like the macro skipped rows rather than failing to classify them.
- Repeating LCase or Trim inside each Case. The test expression is normalised once at the top — doing it again per branch is redundant, and forgetting it on one branch is a bug that only shows on some data.
- Looking for a Switch statement. VBA has no such statement; the Switch() function is a different thing that returns the first value whose paired condition is True, and it evaluates all its arguments.
Frequently asked questions
Does VBA have a switch statement?
Select Case is the equivalent. There is also a Switch() function, but it returns a value rather than branching and evaluates every argument, so it is not a substitute for the block.
Can one Case match a range of numbers?
Yes, two ways: Case 10 To 20 is inclusive of both ends, and Case Is >= 100 handles open-ended comparisons. They can be mixed on one line: Case 1 To 5, Is >= 100.
When is Select Case better than ElseIf?
Whenever a single value is compared against several possibilities — roughly three branches upward. It names the tested expression once, so it cannot drift between branches the way a repeated ElseIf condition can.
Is Case matching case-sensitive for text?
Yes under the default Option Compare Binary. Wrap the test expression in LCase (as in the example) or put Option Compare Text at the top of the module.
Other ways people ask this
People reach this page typing “excel vba switch statement” and “switch case vba excel”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “excel vba switch statement”, “switch case vba excel” all point at the one operation explained on this page, which is why they all lead here.