CODEWITHSUNDEEP
excelvba
Vivek Nath R
Vivek Nath R

Reputation: 171

How to use Multiple case statements in VBA

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?

Please see the below table

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

Upvotes: 1

Views: 92

Answers (2)

Frank
Frank

Reputation: 67

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

Upvotes: 1

user10970498
user10970498

Reputation:

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

Upvotes: 2

Related Questions