View Full Version : Select case using a collection of values

07-07-2019, 03:23 PM
Is there a better way to construct the "Select Case" looping thru a collection?

For example, I have over 30 text values to test and I rather not hard code all 30 in the Case statement like below:

Select CASE Programs
Case "PM", "FC","CT","US","TR"
Msgbox "There is over 30 of these"
Case Else
Msgbox "Is there another way?"
End select

Is there a way to do this without having to type all 30 values separated by comma?

any assistance would be appreciated.

07-07-2019, 04:59 PM
One way

Dim s as string
"PM#FC#CT#US#TR#" etc

If Instr(s, Programs) > 0 then


It'd be easier if the
"PM", "FC","CT","US","TR" etc were in a worksheet somewhere