Excel Functions in VBA

Posted by Tim on December 28, 2001 8:47 AM

I know that some of the simpler Excel functions seem to work in code. But I just tried to write some code which used VLookup as well as Match and VB didn't seem to recognize either of them and I got an error. What's happening? What can I do?

Posted by John on December 28, 2001 9:49 AM

You have to tell VB that this is an Excel function:

Application.WorksheetFunction.Vlookup(.......

Posted by Tom Urtis on December 28, 2001 9:50 AM

Just a thought

You might find the macro recorder to be very useful in this case. Record a macro in which you enter the formula you want, and paste that code into your real module.

T. U.

Posted by Gary Bailey on December 28, 2001 9:53 AM

Could you post your code for us to look at. Its a bit difficult to say otherwise. You should be able to use VLOOKUP and MATCH in VBA.

Gary

Posted by jack in UK on December 28, 2001 10:22 AM

Hi --
Simple i feel to get the code to your formula
record, select cell and press F2 and enter VBA code is converted for you, so you can add it OK

But... be careful as VBA tends to convert to R1C1 offset not A1 A2 etc

Try it, or like has been said before post up your formula...
HTH
Jack