Excel VBA: Search an Array for a value 1

[Aeneas]

Suppose I have made an array in VBA, something like:

ShirtCols = array("red", "beige", "white", "black", "blue")
MyCol = "pink"

I want to see if pink is an acceptable shirt colour as per this array (perhaps representing what I have in my closet right now). I could do:

for i = 0 to 4
if ucase(array(i)) = ucase(MyCol) then
msgbox("Today you are able to wear a " & MyCol & " shirt.")
else
msgbox("There are no " & MyCol & " shirts available to wear.")
end if
next i


But I foresee this being slow and perhaps somewhat cumbersome. I have searched for array and find or lookup and can't find anything on seeing if an array contains any specific element. Is there some sort of function like InStr, but for arrays, that could help me here?

Thanks in advance.

Cheers, Tom

 

[PHV]

If InStr(1, "," & Join(ShirtCols, ",") & ",", "," & MyCol & ",", vbTextCompare) > 0 Then
MsgBox "Today you are able to wear a " & MyCol & " shirt."
Else
MsgBox "There are no " & MyCol & " shirts available to wear."
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Short of a loop as you have construted, if your list of shirt colors were in sheet1!A1:A5

dim n, s as string
s = "pink"
n = Application.Match(s, Sheet1.Range("A1:A5"), 0) 
if iserror(n) then
  'Houston, we have a problem
else
  'no prob!
  MyCol = n
end if

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Aeneas]

Brilliant! Never thought of using InStr, even though I referred specifically to it, but didn't know of Join function until now. Nice touch with the extra commas on the front and back to get a true independent find.

Thanks so much!