[SOLVED] Array match in VBA

Is there a way to implement such a statement in VBA ?

{=MATCH(1;(R_CODES=C_SEARCHCODE)*(R_FROM<=C_SEARCHDATE);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.

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_SEARCHDATE);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.

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_SEARCHDATE);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.

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_SEARCHDATE);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.

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_SEARCHDATE);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.

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_SEARCHDATE);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" <DoctorG@discussions.microsoft.com> wrote in message
news:221AFE85-E318-4955-B784-07CE1FC139F5@microsoft.com...
> 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_SEARCHDATE);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.