worksheetfunction.match

[David Robinson]

Can I use worksheetfunction.match() to look for specified
values a multidimensional array? Is there a functio
within VB that searches for strings or numbers in ranges
or arrays

 

[Bob Phillips]

David,

You can use WorksheetFunction.Match if you want, or you could use the Find
method (check it in VBA Help).

Former would be like

Worksheetfunction.Match(value_to_find, range_to_look_in,0)

The latter is

Set oCell = range_to_look_in.Find(value_to_find)
If N ot oCell Is Nothing Then
' its found so carry one


End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Match doesn't work with a multidimension array except in the special case
where it is one row or one colulmn, just like in a worksheet.

Also, in xl2000 and earlier, you are limited to an array of 5461 elements.
for example:

Sub AAB()
Dim i As Long
Dim varr() As Long
Dim res As Variant
ReDim varr(1 To 5461, 1 To 1)
For i = 1 To 5461
varr(i, 1) = i
Next
res = Application.Match(5461, varr, 0)
MsgBox res
End Sub

works, but changing 5461 to 5462 fails.

 

[Guest]

Thanks. I am having problems configuring .find to an
array eg myArray(10,3)

 

[Tom Ogilvy]

Find only works with a range. Many people use the term array to refer to a
multicell area on a worksheet.

Searching a vb array is pretty fast
Sub AAC()
Dim lrow As Long, i As Long
Dim lcol As Long, j As Long
Dim myArray() As Long
Dim targetval As Long ' or whatever
ReDim myArray(0 To 10, 0 To 3)
For i = 0 To 10
For j = 0 To 3
myArray(i, j) = i * j
Next j
Next i
targetval = 27
lrow = -1
lcol = -1
For i = LBound(myArray, 1) To UBound(myArray, 1)
For j = LBound(myArray, 2) To UBound(myArray, 2)
If myArray(i, j) = targetval Then
lrow = i
lcol = j
Exit For
End If
Next j
If Not lcol Then Exit For
Next i
MsgBox "myarray(" & lrow & ", " & lcol _
& ") = " & myArray(lrow, lcol)
End Sub