Welcome to Excel Avon
SELECT CASE Statement in Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
The select case work exactly like how the if condition work. This article is for Select Case Statement in Excel VBA, In this article we will understand what is the function of Select Case Statement in Excel VBA, Uses of VBA Select Case, Select Case Statement works exactly the same way as to how IF condition works.
SELECT CASE in Excel VBA is one of the useful statements in VBA which helps in decision making process. In this only one expression is used to enumerate the various possible cases.
To understand for Select Case Statement in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
SELECT CASE STATEMENT IN EXCEL VBA
To understand Select Case in Excel VBA, we test the conditions and draw result, after inserting the module in VBE we will write the subroutine for select case in Excel VBA.
Sub UseSelectCase() End Sub
After writing the subroutine we will define the variable
Sub UseSelectCase() Dim i as Long Dim Result as String End Sub
For Inp, write the sheet and the range of cells, and write “Select Case” as word.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
End Sub
Use End Select for Ending case.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
End Select
End Sub
And Now I will make case ‘A’ with result.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
Case "A"
Result = "Excellent"
End Select
End Sub
Make case ‘B’ with result.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
Case "A"
Result = "Excellent"
Case "B"
Result = "Very Good"
End Select
End Sub
Make case ‘C’ with result.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
Case "A"
Result = "Excellent"
Case "B"
Result = "Very Good"
Case "C"
Result = "Average"
End Select
End Sub
Make case ” D”, ” E” & ” F” with result, here we are writing multiple cases.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
Case "A"
Result = "Excellent"
Case "B"
Result = "Very Good"
Case "C"
Result = "Average"
Case "D"
Result = "Bad"
End Select
End Sub
And in the last we will create an Else Case, if the input cell is blank then what will be the result.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
Case "A"
Result = "Excellent"
Case "B"
Result = "Very Good"
Case "C"
Result = "Average"
Case "D"
Result = "Bad"
Case Else
Result = "Not Defined"
End Select
End Sub
Will write the range of the result of the active sheet.
Sub UseSelectCase()
Dim i as Long
Dim Result as String
Inp = Sheet.Range("B2").value
Select Case Inp
Case "A"
Result = "Excellent"
Case "B"
Result = "Very Good"
Case "C"
Result = "Average"
Case "D"
Result = "Bad"
Case Else
Result = "Not Defined"
Activesheet.Range("B3").Value = Result
End Select
End Sub
When the code is written then we will go to our worksheet and then insert a button, assign a macro, then rename the button.
You can change the name of the button according to you like we have given show result.
After the button is assigned, we will fill the case in the Inp cell and find the result. We filled case A in Inp cell and clicked on show result button and the result was found excellent
Similarly, we will do in multi case, we filled case D in Inp cell and clicked on show result button and the result was found Bad.
Suppose now we fill a case which is neither defined nor result then. We have filled X case and result is not defined.
SELECT CASE Statement with Number
In second example we will use select case Statement with use number, we will write the subroutine for select case with Number in Excel VBA.
Sub UseWithNumber() End Sub
After writing the subroutine we will define the variable
Sub UseSelectCase() Dim Inp as Variant Dim Result as String End Sub
For Inp, write the sheet and the range of cells, and write “Select Case” as word.
Sub UseSelectCase()
Dim Inp as Variant
Dim Result as String
Inp = Sheet.Range("B7").value
Select Case Inp
End Sub
Use End Select for Ending case.
Sub UseSelectCase()
Dim Inp as Variant
Dim Result as String
Inp = Sheet.Range("B7").value
Select Case Inp
End Select
End Sub
And Now I will make First case for ‘>80’ with result.
Sub UseSelectCase()
Dim Inp as Variant
Dim Result as String
Inp = Sheet.Range("B7").value
Select Case Inp
Case is > 80
Result = "Excellent"
End Select
End Sub
Now I make Second case for ‘>60’ with result.
Sub UseSelectCase()
Dim Inp as Variant
Dim Result as String
Inp = Sheet.Range("B7").value
Select Case Inp
Case is > 80
Result = "Excellent"
Case is > 60
Result = "Good"
End Select
End Sub
Make case third with result. third case is ‘> 40
Sub UseSelectCase()
Dim Inp as Variant
Dim Result as String
Inp = Sheet.Range("B7").value
Select Case Inp
Case is > 80
Result = "Excellent"
Case is > 60
Result = "Good"
Case is > 40
Result = "Average"
End Select
End Sub
And in the last we will create an Else Case, if the input cell is blank then what will be the result.
Sub UseSelectCase()
Dim Inp as Variant
Dim Result as String
Inp = Sheet.Range("B7").value
Select Case Inp
Case is > 80
Result = "Excellent"
Case is > 60
Result = "Good"
Case is > 40
Result = "Average"
Case Else
Result = "Bad"
End Sub
Will write the range of the result of the active sheet.
Sub UseSelectCase()
Dim Inp as Variant
Dim Result as String
Inp = Sheet.Range("B7").value
Select Case Inp
Case is > 80
Result = "Excellent"
Case is > 60
Result = "Very Good"
Case is > 40
Result = "Average"
Case Else
Result = "Bad"
End Select
Activesheet.Range("B8"). Value = Result
End Sub
When the code is written then we will go to our worksheet and then insert a button, assign a macro, then rename the button.
You can change the name of the button according to you like we have given show result.
After the button is assigned, we will fill the case in the Inp cell and find the result. We filled number 45 in Inp cell and clicked on show result button and the result was found Average.
We filled Number 75 in Inp cell and clicked on show result button and the result was found Good.
We filled Number 10 in Inp cell and clicked on show result button and the result was found bad.
