Array MATCH function for VBA

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I have a 2d VBA array. It is 1 row and 10 columns. It is filled with
dates. I want to return the position of a date variable within the
array. Similar to what I would do with a MATCH function in Excel.

How do you do this in VBA.
 
I'm not sure why if there's only one "row" you're using a multi-dimensional
array. But, do you mean something like this? (I'm just using strings
instead of dates, but the concept is the same.)


Sub Something()
Dim x(0, 9) As String
Dim sTheValueIwantToMatch as String

x(0, 0) = "a"
x(0, 1) = "b"
x(0, 2) = "c"
x(0, 3) = "d"
x(0, 4) = "e"
x(0, 5) = "f"
x(0, 6) = "g"
x(0, 7) = "h"
x(0, 8) = "i"
x(0, 9) = "j"

sTheValueIwantToMatch = "c"

For i = LBound(x, 2) To UBound(x, 2)
If x(0, i) = sTheValueIwantToMatch Then
Debug.Print "It matches at index " & i
Exit For
End If
Next i
End Sub
 
Dim dtVal as Date
dtVal = DataValue("01/22/2004")
res = application.Match(clng(dtVal),MyArray,0)

if not iserror(res) then
msgbox "Index is " & res - 1
end if

res will be 1 based. If your array is zero based, subtract 1
 
Tom,

That's an amazing shortcut for quickly searching a 1D array. I like it.

Do you know if it's possible to search a 2D array that way?


--
Rob van Gelder - http://www.vangelder.co.nz/excel


Tom Ogilvy said:
Dim dtVal as Date
dtVal = DataValue("01/22/2004")
res = application.Match(clng(dtVal),MyArray,0)

if not iserror(res) then
msgbox "Index is " & res - 1
end if

res will be 1 based. If your array is zero based, subtract 1
Click to expand...
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
want to consider the ArrayMatch function. It will return an array of the
row index and column index numbers within the array (or range) where
matches are found, or, for a range, with use of the optional 4th
argument it will return an array of the worksheet addresses of the matches.

Alan Beban
 
Not with match - it is the match worksheet function, so has the same
restriction of searching a single column or single row (or the vba
equivalent ex: 1D array for a 2D array like myarray(1 to n, 1 to 1)

See Alan's post.

--
regards,
Tom Ogilvy

Rob van Gelder said:
Tom,

That's an amazing shortcut for quickly searching a 1D array. I like it.

Do you know if it's possible to search a 2D array that way?
Click to expand...
 
I continue to tidy it up. The more feedback I get on the utility or
non-utility of the functions, the more useful I can try to make them.

Alan Beban
 
Alan,

I thought I would reply off of the groups, I can't have got your e-mail
right after omitting the no spam...

Could you please explain how to send you e-mail?
 
Back
Top