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

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


"Colin Macleod" wrote in message
...
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