Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Second Most Common Value | Excel Discussion (Misc queries) | |||
Combining two ranges based on common a common value | Excel Discussion (Misc queries) | |||
Most and least common | Excel Discussion (Misc queries) | |||
Common footer but not common margins please -(Page 1 of 2) etc | Excel Discussion (Misc queries) | |||
common subroutines - add-in | Excel Programming |