Ian,

' If you set row_num or column_num to 0 (zero), then
' Index returns the array of values for the entire column or row
' (looping thru the array column could be as fast or faster)

Sub MakeSomethingUp()
Dim N As Variant
Dim arr As Variant
Dim arr2 As Variant
Dim strMessage As String

'Fill the array.
arr = Range("A1:B50").Value

'Return the second column of the array
arr2 = Application.Index(arr, 0, 2)
'Find the position in the array of "77"
N = Application.Match(77, arr2, 0)
If Not IsError(N) Then
strMessage = "Found in row " & N
Else
strMessage = "Not Found"
End If
MsgBox strMessage
End Sub
-----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"IanKR"
wrote in message
I often have to test whether any of a range of numbers (x in the example
below) is represented in a set of values - which may or may not be entered
in a range of cells. If I put the set of values into an array (ArrValues
below - could be one- or two-dimensional), is there a neat way of asking:

If x "is a member of" ArrValues

on one line of code, or do I have to cycle through all the members of
ArrValues in turn (say using For loop) and check whether each one is equal
to x? I've tried using the Match WorksheetFunction with 0 as the third
argument:

With WorksheetFunction
For x = 1 To 100
If Not .IsNA(.Match(x, ArrValues, 0)) Then
Cells(x, 14) = x 'report matching values in column 14
End If
Next k
End With

but it doesn't work - I get an "Unable to get the Match property of the
WorksheetFunction class".
Instead, I'm having to do this, for example:

For i = 1 To 10
For j = 1 To 10
For x = 1 To 100
If ArrValues(i, j) = x Then
Cells(x, 14) = x 'report values in column 14
End If
Next k
Next j
Next i

The example may seem trivial, but I have other instances where it's not so
trivial.
Thanks
Ian