Array MATCH function for VBA
 

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.


---
Message posted from http://www.ExcelForum.com/

Array MATCH function for 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


--

Ray at home
Microsoft ASP MVP


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


---
Message posted from http://www.ExcelForum.com/

Array MATCH function for VBA
 

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

--
Regards,
Tom Ogilvy


ExcelMonkey wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

Array MATCH function for VBA
 

And you have a couple of other responses to your first post.

"ExcelMonkey <" wrote:

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.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Array MATCH function for VBA
 

Looks like a different situation. This one is one row.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
And you have a couple of other responses to your first post.

"ExcelMonkey <" wrote:

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.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Array MATCH function for VBA
 

Oops.

Sorry.

Tom Ogilvy wrote:

Looks like a different situation. This one is one row.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
And you have a couple of other responses to your first post.

"ExcelMonkey <" wrote:

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.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson


--

Dave Peterson

Array MATCH function for VBA
 

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

--
Regards,
Tom Ogilvy


ExcelMonkey wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

Array MATCH function for VBA
 

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

Rob van Gelder wrote:
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?

Array MATCH function for VBA
 

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

--
Regards,
Tom Ogilvy


ExcelMonkey wrote in
message
...
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.


---
Message posted from http://www.ExcelForum.com/

Array MATCH function for VBA
 

Tom, Thanks.

Guessed as much... still handy for 1D lookup.


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


"Tom Ogilvy" wrote in message
...
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 wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


ExcelMonkey wrote in
message
...
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.


---
Message posted from http://www.ExcelForum.com/

Array MATCH function for VBA
 

Alan, Thanks.

You certainly have put a lot of effort into this collection.

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


"Alan Beban" wrote in message
...
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

Rob van Gelder wrote:
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?

Array MATCH function for VBA
 

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

Rob van Gelder wrote:
Alan, Thanks.

You certainly have put a lot of effort into this collection.

Array MATCH function for VBA
 

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?


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


"Alan Beban" wrote in message
...
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

Rob van Gelder wrote:
Alan, Thanks.

You certainly have put a lot of effort into this collection.

Array MATCH function for VBA
 

Rob,

Did you receive an email from me from my proper address?

Alan Beban

Rob van Gelder wrote:
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?