VBA: Select Case

Alright VBA users... Have you ever needed to to use and if/then statement with multiple criteria and need to run a lot of code after the criteria is met?  Well, Select Case is the code for you!

It looks like this:

Select Case [What you are evaluating]
   Case is = [potential value]
       'your code goes here
   Case is = [potential value]
       'your code goes here
   Case is = [potential value]
       'your code goes here
   Case Else
       'your code goes here
End Select

Let's put it to use.  In this example, I am going to evaluate the value within cell B1.  In my code, I personally prefer using the "Cells()" reference method over the "Range()" reference method.  Cells(row, col) is what you'll see.

Select Case Sheets("Sheet1").cells(1, 2).value - This opens the "Select Case" Statement and shows that we're looking at the value of cell B1 or row1, column2.

Next, if the cell value is 1, 2, or 3 I want run specific code depending on the value.
Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]

So what if the cell value isn't 1-3?  Well, in that "case" (Pun!) you will use the "Case Else" Statement.

Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]
   Case Else
      [insert code here]

Finally, when you are done adding cases and code, you need to close the Select Case Statement with "End Select"

Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]
   Case Else
      [insert code here]
End Select

**Tips and tricks:
#1 - if you are going to be using VBA, pay attention to the formatting of the code.  Think of your code as an onion (multiple layers, not that they stink or make you cry).  Your opening statement is the outer layer (no indentation).  As you move into the statement - for example from the "Select Case ..." to "Case is =..." you will press tab and indent to indicate that what is happening is happening within the Select Case Statement. The code you include if a statement is True would be indented twice.  The "End Select" statement is referring to the "outer layer" so it is not indented to complete visual.

My code above in Blue is an example of this.

#2 - If you do not intend for anything to happen when a condition is met using the Select Case statement, simply do not place any code in the [insert code here] section and the program will resume by ending the select statement and continuing to the next line.