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.

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.

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.

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.

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.

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.