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 select 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. 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
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. 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 select 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
On the way here you may have searched this as “excel vba case statement”, “select case in excel vba” and “excel vba select worksheet” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “excel vba case statement”, “select case in excel vba”, “excel vba select worksheet” all point at the one operation explained on this page, which is why they all lead here.