Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Array (MATCH function?) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
1:1 as the Array using the MATCH function | New Users to Excel | |||
Match as well as does not match array function | Excel Discussion (Misc queries) |