Worksheet Functions

[alistre]

I am trying to codify Excel's Match function in VBA. Somewhere I have
a syntax error. I cannot figure it out. I am just learning about the
"Application.WorksheetFunction" syntax. Can you help me with writing
the Match argument?

Range("A10") =
Application.WorksheetFunction.Match(Range("L8").Text,Range("A1:A550"),0)

 

[Norman Jones]

Hi Alistre,

Try:

Dim Res As Variant

Res = Application. _
Match(Range("L8").Value, Range("A1:A550"), 0)
Range("A10").Value = Res

 

[alistre]

Thanks a lot. That worked perfect. I'm curious why the
"Application.WorksheetFunction" part was shortened to "Application".
Also, although the code worked, VBA didn't seem to recognize that
"Match" could follow "Application" when I tried to use Complete Word.
Do you know either answer? Thanks again.

 

[Norman Jones]

Hi Alistre,

See:

http://tinyurl.com/o7lpe

or perform a google groups search using Ttappale error and WorksheetFuntion
as keys.

 

[Norman Jones]

Hi Alistre,

or perform a google groups search using Ttappale error and
WorksheetFuntion
as keys.

Turning on the light while I type:

or perform a google groups search using 'Trappable error' and
'WorksheetFuntion'
as keys.

 

[Norman Jones]

Hi Alistre,

See also Tom Ogilvy's response in your 'Using Excel functions in VBA'
thread.