Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorksheetFunction help | Excel Discussion (Misc queries) | |||
WorksheetFunction with VBA | Excel Discussion (Misc queries) | |||
Application.WorksheetFunction.Match problem | Excel Worksheet Functions | |||
worksheetfunction | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |