Common Formulas in VBA
 

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 i
has finished running it will display the vlookup formula at the rang
that I have specified.

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

For example I will use a range object to specifiy where the formul
will be added, a range object to specify the lookup value, a rang
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 stuc
for a day now

Many thanks

Phi

--
philwongn
-----------------------------------------------------------------------
philwongnz's Profile: http://www.excelforum.com/member.php...fo&userid=2628
View this thread: http://www.excelforum.com/showthread.php?threadid=39997

Common Formulas in VBA
 

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

Common Formulas in VBA
 

Not sure I fully get it but maybe this will get you started

Set rng1 = Range("A1")
Set rng2 = Range("B1")
Set rng3 = Range("M1:P20")

rng1.Formula = "=VLOOKUP(" & rng2.Address & "," & rng3.Address &
"2,False)"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"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

Common Formulas in VBA
 

Thank you very much for your help!

I am almost there, however with the table array in the vlookup, will i
be possible to refer to another workbook, hence another worksheet?
tried referring the table array in the vlookup as follows:

myRngCell.Value = "=VLOOKUP(" & myRngLP.Address & ",*"
workbooks("temp.xls").worksheets("sheet1").myRngTa ble.Address &",
2)"

Many thanks!

Phi

--
philwongn
-----------------------------------------------------------------------
philwongnz's Profile: http://www.excelforum.com/member.php...fo&userid=2628
View this thread: http://www.excelforum.com/showthread.php?threadid=39997

Common Formulas in VBA
 

myRngCell.FormulaR1C1 = "=VLOOKUP(" & _
myRngLP.Address(1,1,xlR1C1,False) & "," & _
myRngTable.Address(0,0,xlR1C1,True) & ",2)"



--
Regards,
Tom Ogilvy


"Mangesh Yadav" wrote in message
...
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

Common Formulas in VBA
 

Thank you all for your replies, I finally did it. Saves me a lot of time
finding it out myself.

Thanks Again!


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