Arrays

C

Colin Macleod

Is there a function which can check whether a particular value occurs in an
array? For example, if the array contained the elements "red" "orange" and
"green" then a check against "red" would come up with "true" whereas "blue"
would come up with "false". I realise I could loop through each element in
turn - I wondered whether there was a quicker way.

Thanks

Colin Macleod
 
C

Chip Pearson

Colin,

You can use Application.Match to test for the existence of an
item in an array. For example,

Dim S(1 To 3) As String
Dim Res As Variant
S(1) = "red"
S(2) = "green"
S(3) = "blue"
Res = Application.Match("blue", S, 0)
If IsError(Res) Then
Debug.Print "Not in array"
Else
Debug.Print "Item: " & Res
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Top