<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Select Case Statement in Excel VBA

| | Information Helpful? Why Not Donate.

Excel VBA: Select Case Statement in Excel VBA Macro Code. No More Multiple If/Else Statements

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

One of the best methods for multiple conditions is the Select Case Statement. It does away with the need for multiple If Statements which can make Excel VBA code very hard to read and decipher. The Select Case Statement has a syntax of:

Select Case < Expression to test>    Case       Do something    Case Else      Do something elseEnd Select

As you can see the Select Case Statement is very similar to the If Statement in that it will only perform some action if/when a condition is met. However, as you will learn, the Select Case is far more flexible. Let's look at the "Select Case" Statement in it's simplest form.

Sub TheSelectCase1()    Select Case Range("A1").Value          Case 100              Range("B1") = 50    End SelectEnd Sub

Now let us say you need to perform any one of 5 actions depending on the Value of Range A1. If so we could use;

Sub TheSelectCase2()    Select Case Range("A1").Value          Case 100              Range("B1").Value = 50          Case 150              Range("B1").Value = 40          Case 200              Range("B1").Value = 30          Case 350              Range("B1").Value = 20          Case 400              Range("B1").Value = 10    End SelectEnd Sub

This, in my opinion, is a far better structure and easier to read than an If Statement with multiple ElseIf Statements. If none of the above Conditions were met, nothing would occur, unless we use the optional Case Else Statement, like:

Sub TheSelectCase3()    Select Case Range("A1").Value          Case 100              Range("B1").Value = 50          Case 150              Range("B1").Value = 40          Case 200              Range("B1").Value = 30          Case 350              Range("B1").Value = 20          Case 400              Range("B1").Value = 10          Case Else              Range("B1").Value = 0    End SelectEnd Sub

So if the value of range A1 is NOT 100,150,200,350 or 400 then place a Value of 0 (zero) in Range B1.

Now while this demonstrates how we can check multiple conditions with the Select Case Statement, what if we want to perform some action if the Range A1 is equal to any one of the Values 100,150,200,350 or 400. If this is the case (no pun intended) we could use:

Sub TheSelectCase4()    Select Case Range("A1").Value          Case 100, 150, 200, 350, 400              Range("B1").Value = Range("A1").Value          Case Else              Range("B1").Value = 0    End SelectEnd Sub

Let's assume that you only want to perform some action if the range(s) you are checking are between 2 numbers. If this is the case (excuse the pun) then you could use:

Sub TheSelectCase5()    Select Case Range("A1").Value        Case 100 To 500             Range("B1").Value = Range("A1").Value        Case Else             Range("B1").Value = 0      End SelectEnd Sub

In this instance, if the range A1 contains a number => 100 and =< 500 then cell A1 value will be placed into cell B1.

Now, what about if we needed to check if cell A1 was not only between 100 and 500 but also between 700 and 1000, 1500 and 2000? No problem, with the Select Case you would simply use:

Sub TheSelectCase6()    Select Case Range("A1").Value        Case 100 To 500, 700 To 1000, 1500 To 2000             Range("B1").Value = Range("A1").Value       Case Else             Range("B1").Value = 0     End SelectEnd Sub

In other words if cell A1 contains a number (eg 600) that does not meet the Case criteria, B1 will equal 0. By-the-way, the limit using a Select Case like this is far from 3 criteria! You can also mix and match the criteria you use in a Select Case Statement, so if you wanted to include some text and/or specific numbers you could use:

Sub TheSelectCase()    Select Case Range("A1").Value           Case 100 To 500, 652, 700 To 1000, 1233, 1500 To 2000, "dog", "cat"                 Range("B1").Value = Range("A1").Value           Case Else                 Range("B1").Value = 0     End SelectEnd Sub

Select Case Knows its Alphabet

The last benefit we will look at with the Select Case Statement is how we can use it on text and determine if it lies between other text in an alphabetical sense

Sub TheSelectCase7()    Select Case Range("A1").Text          Case "aardvark" To "elephant"                 Range("B1").Value = "it's between"          Case Else                 Range("B1").Value = "it's not between"         End SelectEnd Sub

What this example will do is check whether the Text in cell A1 falls between "aardvark" and "elephant" in an alphabetic list. So if A1 had the text "zebra" B1 would equal "it's not between", however if it contained "cat" B1 would equal "it's between". One point that should be made here is that, by default, VBA code like this, is case sensitive (Binary). So if cell A1 had the text "Cat" as apposed to "cat" B1 would equal "it's not between". This because the internal binary representations of the characters in Windows are seen in a table something like:
A < B < E < Z < a < b < e < z < � < � < � < � < � < �

This can at times cause unexpected results, but like most parts of Excel we can change this. See Stop Case Sensitivity in Excel VBA

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft