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. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel vba switch”. 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. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
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. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “excel vba switch” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
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.