Case syntax with VBA Excel

Question

I am trying evaluate each cell and if the value in A is 01,06,42,66 and the value in B is Outside, then DoStuff.

With the following case statement, it only picks up 66 not the rest. I could write a case statement for each combination, but that would be too much. Do you have any suggestions on how to simplify this?

Select Case .Range("A" & i).Value A & .Range("B" & i).Value 
Case "01","06","42","66" & "Outside" 
    DoStuff
End Select

Scott Craner · Accepted Answer · 2017-10-24 16:56:38Z

3

put an if in your Select Case:

Select Case .Range("A" & i).Value    
Case "01","06","42","66"
    If .Range("B" & i).Value = "Outside" Then
        DoStuff
    End If
End Select

Or you will need to concatenate all choices:

Select Case .Range("A" & i).Value  & .Range("B" & i).Value 
Case "01Outside","06Outside","42Outside","66Outside"
    DoStuff
End Select

answered Oct 24, 2017 at 16:56

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

Case syntax with VBA Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related