worksheetfunction.match
 

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

worksheetfunction.match
 

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)

"David Robinson" wrote in message
...
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

worksheetfunction.match
 

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.

--
Regards,
Tom Ogilvy

Bob Phillips wrote in message
...
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)

"David Robinson" wrote in message
...
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

worksheetfunction.match
 

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

-----Original Message-----
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)

"David Robinson"
wrote in message
...
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


.

worksheetfunction.match
 

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


--
Regards,
Tom Ogilvy



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

-----Original Message-----
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)

"David Robinson"
wrote in message
...
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


.