VBA: Search In Arrays

[AD_Taylor]

Hi,

Is there a quick way to search, for example a string array, to see if it contains a searched for string?

Something like:

Array = cat, dog, pigeon, horse, rabbit
Search for any of these, e.g. pigeon returns True?

Theres the obvious loop through the array with an If statement and a Boolean variable to record whether the value is found, but wondered if something quicker and easier was available?

Thanks,
Adam

 

[VoG]

Here's a way without looping

Sub ary()
Dim s
s = Array("cat", "dog", "pigeon", "horse", "rabbit")
MsgBox IsNumeric(Application.Match("pigeon", s, 0))
End Sub

 

[AD_Taylor]

Great this works perfectly Peter.

I assume this will be able to check for arrays of any size?

 

[VoG]

Yes, one-dimensional arrays of any size.