How to use Multiple case statements in VBA

Question

I have two columns lets say A and B. In column A I've values like Apple, banana, Brinjal and in column B Ripe and Not Ripe. In column C I want to check if it is a fruit or vegetable and then riped or not riped. I want the below result.

How to use multiple Case statements?

Private Sub CommandButton1_Click()
     Dim category As String, result As String
     For i = 2 To 1000
         category = Range("A" & i).Value
         Select Case category
             Case "Apple"
                   result = "Fruit"
             Case "Brinjal"
                   result = "Vegetable"
         End Select
         Range("C" & i).Value = result
     Next
End Sub

Vasily · Accepted Answer · 2019-01-27 06:52:15Z

2

you can specify the list of values using comma:

Private Sub CommandButton1_Click()
    Dim category As String, result As String
    For i = 2 To 1000
        category = Range("A" & i).Value
        Select Case category
            Case "Apple", "Banana", "Orange"
                result = "Fruit"
            Case "Brinjal"
                result = "Vegetable"
            Case else
                result = vbnullstring
            End Select
        Range("C" & i).Value = Range("B" & i).Value & " " & result
    Next i
End Sub

edited Jan 27, 2019 at 6:52

Vasily

5,7923 gold badges22 silver badges36 bronze badges

answered Jan 27, 2019 at 6:50

user10970498

Sign up to request clarification or add additional context in comments.

1 Comment

Vivek Nath R Over a year ago

Hi, Thanks for your input

Frank · Accepted Answer · 2019-01-27 06:56:27Z

1

If i well understand...

For i = 1 To 4
    category = Range("A" & i).Value
    Select Case category
        Case "Apple", "Orange", "Banana"
            result = "Fruit"
        Case "Brinjal","xxx"
            result = "Vegetable"
        Case Else
            result = ""
    End Select

    'If th onlys status possibles are ripe and not riped

    Range("C" & i).Value = Range("B" & i).Value & " " & result
Next

answered Jan 27, 2019 at 6:56

Frank

6711 bronze badges

Collectives™ on Stack Overflow

How to use Multiple case statements in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related