How to use SELECT CASE statement
The SELECT CASE statement allows you to compare an expression to multiple values.
It is similar to the IF THEN ELSE statement except that the IF THEN ELSE statement can evaluate multiple expressions. The SELECT CASE statement evaluates one expression.
Table of Contents
1. SELECT CASE statement Syntax
Select Case testexpression
[ Case expressionlist-n [ statements-n ]]
[ Case Else [ elsestatements ]]
End Select
2. SELECT CASE statement Parameters
| testexpression | Required. Any expression:
numeric expression - You can use variables, constants, keywords, and operators. The result is a number. string expression - Any expression that returns a string. |
| expressionlist-n | Required.
True - ignore empty cells in the third argument. False - adds empty cells to the result. |
| statements-n | Required. The cell range you want to concatenate. |
| elsestatements | Optional. Up to 254 additional cell ranges. |
3. SELECT CASE statement example 1
The User Defined Function above compares the score to several criteria and returns the corresponding grade.
The UDF is entered in column D and takes the score from column C and returns the grade.
UDF in cell D3:
VBA Code
Function Grade(score) Select Case score Case Is < 60 Grade = "F" Case 61 To 64 Grade = "D" Case 65 To 69 Grade = "D+" Case 70 To 74 Grade = "C" Case 75 To 79 Grade = "C+" Case 80 To 84 Grade = "B" Case 85 To 89 Grade = "B+" Case 90 To 94 Grade = "A" Case Else Grade = "A+" End Select End Function
4. SELECT CASE statement example 2
Formula in cell D3:
VBA Code
Function PriceSize(size)
Select Case size
Case Is = "Small"
PriceSize = 100
Case Is = "Medium"
PriceSize = 120
Case Is = "Large"
PriceSize = 135
End Select
End Function
5. SELECT CASE statement - VBA alternative
Formula in cell D3:
VBA Code
Function Grade1(score)
If score < 60 Then
Grade1 = "F"
ElseIf score <= 64 And score >= 61 Then
Grade1 = "D"
ElseIf score <= 65 And score >= 69 Then
Grade1 = "D+"
ElseIf score <= 74 And score >= 70 Then
Grade1 = "C"
ElseIf score <= 79 And score >= 75 Then
Grade1 = "C+"
ElseIf score <= 84 And score >= 80 Then
Grade1 = "B"
ElseIf score <= 89 And score >= 85 Then
Grade1 = "B+"
ElseIf score <= 94 And score >= 90 Then
Grade1 = "A"
Else
Grade1 = "A+"
End If
End Function
6. SELECT CASE statement - Excel Function alternative
Formula in cell D3:
Get Excel *.xlsm file
How to use SELECT CASE statement.xlsm
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form