Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array match in VBA
Is there a way to implement such a statement in VBA ?
{=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCH DATE);0)} This works in a cell but I need to create a VBA function that retrieves a value based on arguments and use it in a subroutine. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array match in VBA
v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)"
-- Regards, Tom Ogilvy "DoctorG" wrote: Is there a way to implement such a statement in VBA ? {=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCH DATE);0)} This works in a cell but I need to create a VBA function that retrieves a value based on arguments and use it in a subroutine. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array match in VBA
That simple????..... Thanks Tom
"Tom Ogilvy" wrote: v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)" -- Regards, Tom Ogilvy "DoctorG" wrote: Is there a way to implement such a statement in VBA ? {=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCH DATE);0)} This works in a cell but I need to create a VBA function that retrieves a value based on arguments and use it in a subroutine. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array match in VBA
IShould be - almost, I left off the closing paren:
v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)") and I am pretty sure that it should be entered as a US English syntax - although I don't have a foreign version to test with. Test it with some simple formulas and see what it will accept. Evaluate is like a virtual cell. Most things that will work in a cell will work with evaluate if that sheet is active - including array formulas. -- Regards, Tom Ogilvy "DoctorG" wrote: That simple????..... Thanks Tom "Tom Ogilvy" wrote: v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)" -- Regards, Tom Ogilvy "DoctorG" wrote: Is there a way to implement such a statement in VBA ? {=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCH DATE);0)} This works in a cell but I need to create a VBA function that retrieves a value based on arguments and use it in a subroutine. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array match in VBA
Tom, Evaluate worked but I had to construct the string embedding the actual
values: evaluate_string = "MATCH(1,(R_CODES=" & search_code & ")*(R_FROM<=" & search_date & "),0)" result = Evaluate(evaluate_string) Otherwise I would get a 2042 error, obviously something to do with the scope of the variables used. Thanks a lot!! "Tom Ogilvy" wrote: IShould be - almost, I left off the closing paren: v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)") and I am pretty sure that it should be entered as a US English syntax - although I don't have a foreign version to test with. Test it with some simple formulas and see what it will accept. Evaluate is like a virtual cell. Most things that will work in a cell will work with evaluate if that sheet is active - including array formulas. -- Regards, Tom Ogilvy "DoctorG" wrote: That simple????..... Thanks Tom "Tom Ogilvy" wrote: v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)" -- Regards, Tom Ogilvy "DoctorG" wrote: Is there a way to implement such a statement in VBA ? {=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCH DATE);0)} This works in a cell but I need to create a VBA function that retrieves a value based on arguments and use it in a subroutine. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array match in VBA
I had no way of knowing whether those were variables or defined names. If
defined names, as I wrote it would be good. If they are variables used in your code, then what you say is true. However, if I had posted a question and said: ----------------------------------------------------------- Is there a way to implement such a statement in VBA ? {=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCH DATE);0)} ------------------------------------------------------------ What do you think you would think SearchCode and C_SearchDate were? Not complaining, but just to point out something you should consider if you post future questions. -- Regards, Tom Ogvily .. "DoctorG" wrote in message ... Tom, Evaluate worked but I had to construct the string embedding the actual values: evaluate_string = "MATCH(1,(R_CODES=" & search_code & ")*(R_FROM<=" & search_date & "),0)" result = Evaluate(evaluate_string) Otherwise I would get a 2042 error, obviously something to do with the scope of the variables used. Thanks a lot!! "Tom Ogilvy" wrote: IShould be - almost, I left off the closing paren: v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)") and I am pretty sure that it should be entered as a US English syntax - although I don't have a foreign version to test with. Test it with some simple formulas and see what it will accept. Evaluate is like a virtual cell. Most things that will work in a cell will work with evaluate if that sheet is active - including array formulas. -- Regards, Tom Ogilvy "DoctorG" wrote: That simple????..... Thanks Tom "Tom Ogilvy" wrote: v = Evaluate("MATCH(1,(R_CODES=C_SEARCHCODE)*(R_FROM<= C_SEARCHDATE),0)" -- Regards, Tom Ogilvy "DoctorG" wrote: Is there a way to implement such a statement in VBA ? {=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCH DATE);0)} This works in a cell but I need to create a VBA function that retrieves a value based on arguments and use it in a subroutine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH() on a 2D array? | Excel Discussion (Misc queries) | |||
Match using array of column and row references to match with | Excel Worksheet Functions | |||
Match & array | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) |