myITSchools

Select Case statement in excel VBA

• The CASE statement is a built-in function in Excel that is categorized as a Logical Function.
• Executes one of several groups of statements, depending on the value of an expression.

Syntax:-

Select Case test_expression
Case expression_1
 statements
Case expression_2
 Statements
Case expression_n
 statements
Case Else
 statements
End Select

For Example,

An Example to illustrate the which days is according to input by user’ s weekday number

Sub testweekday()

Dim weekday As Integer

   weekday = InputBox("Enter any number(weekday) from 1 to 7")
   
   Select Case weekday
   
   Case 1
   MsgBox "first days of weekday is Sunday"
   Case 2
     MsgBox "Second days of weekday is Monday"
   Case 3
     MsgBox "Third days of weekday is Tuesday"
   Case 4
     MsgBox "Fourth days of weekday is Wednesday"
   Case 5
     MsgBox "Fifth days of weekday is Thursday"
   Case 6
     MsgBox "Sixth days of weekday is Friday"
   Case 7
    MsgBox "Seventh days of weekday is Saturday"
    Case Else
    MsgBox "weekdays falls between 1 to 7 only"
    
    End Select

End Sub

Use Select Case with Multiple Conditions

'We can also specify multiple values to test for the condition.
'In the below code, each case has more than one numbers to test.

Sub Select_ID()
Select Case Range("B4").Value
Case 1, 5, 9, 6
Range("C4").Value = "ID range between 1 to 10"

Case 11, 15, 19, 16
Range("C4").Value = "ID range between 1 to 20"

Case Else
Range("C4").Value = "Please type valid value"
End Select
End Sub

An Example to illustrate the given students got which grade according to marks or percentage

Sub testmarks()
Dim marks As Integer
marks = InputBox("Enter mark or percentage from 1 to 100?")
   Select Case marks
      Case 1 To 35
         MsgBox "You are Fail!"
      Case 36 To 55
         MsgBox "You have got D Grade"
      Case 56 To 69
         MsgBox " You have got C Grade "
      Case 70 To 85
         MsgBox " You have got B Grade "
      Case 86 To 100
         MsgBox " You have got A Grade "
      Case Else
         MsgBox "Your Marks Percentage or marks should be between 1 to 100"
   End Select
End Sub

Using Select case example as string

' Select Player Category according to name

Sub Select_Player()
Select Case Range("B8").Value
Case "Rohit", "Virat", "Rahul", "Gill", "Shreyas"
Range("C8").Value = "Batsman"

Case "Jadeja", "Thakur", "Ashwin"
Range("C8").Value = "All Rounder"

Case "Bumrah", "Siraj", "Shami", "Kuldeep"
Range("C8").Value = "Bowler"

Case "Surya", "Ishan", "Sunder"
Range("C8").Value = "Reserve Player"

Case Else
Range("C8").Value = "Not selected for  world cup"

End Select
End Sub
Scroll to Top