Are you looking for something like this:

Sub test()

Set myRngLP = Range("A1")
Set myRngCell = Range("A10")
Set myRngTableStart = Range("B1")
Set myRngTable = Range(myRngTableStart,
myRngTableStart.End(xlToRight).End(xlDown))

myRngCell.Value = "=VLOOKUP(" & myRngLP.Address & "," & myRngTable.Address &
", 2)"
myRngCell.Offset(0, 1) = "VLOOKUP(" & myRngLP.Address & "," &
myRngTable.Address & ", 2)" 'displays formula used



'cell A10" =VLOOKUP(A1,B1:C9,2)
' considering your range is from B1 to B9, lookup in A1, and show the
formula is A10

End Sub






"philwongnz" wrote
in message ...

Hi Guys,

I am struggling to get the syntax right with common formulas in VB.
For example I would like to use Vlookup in a macro in which after it
has finished running it will display the vlookup formula at the range
that I have specified.

I know I can achieve this by using
-range.FormulaR1C1-="=vlookup(....)", but to make it slightly more
complicated I am using range objects as references rather than the R1C1
format.

For example I will use a range object to specifiy where the formula
will be added, a range object to specify the lookup value, a range
object with .End(xlToRight).End(xlDown) to select the table array.

Can somebody pls give me a hand as to how I can do this - been stuck
for a day now

Many thanks

Phil


--
philwongnz
------------------------------------------------------------------------
philwongnz's Profile:
http://www.excelforum.com/member.php...o&userid=26283
View this thread: http://www.excelforum.com/showthread...hreadid=399971