worksheetfunction.match

  • Thread starter Thread starter David Robinson
  • Start date Start date
D

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
 
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)
 
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.
 
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
 
Back
Top