Referencing another workbook in equation
In VBA I am placing a formula into a cell to copy down.
The equation is something like ActiveCell.FormulaR1C1 = "=Vlookup(value(RC[-4]), [tempworkbook]terms!A:F,5,false)" I have specified the other workbook as Tempbook (using Tempbook = ActiveWorkbook.name) as this is an uncontrolled variable. the Excel formula should end up looking like (assuming tempbook is called book9) =Vlookup(Value(A10),[book9]Terms!$A:$F,5,FALSE) I know its something to do with the workbook location (I think) how do I get this to work. Thanks Dave |
Referencing another workbook in equation
Since Tempbook is a variable, which won't be evaluated inside a string,
you must concatenate your formula. Also, if you're using R1C1 notation, then A:F won't work. Try: ActiveCell.FormulaR1C1 = "=Vlookup(value(RC[-4]),'[" & _ Tempbook & "]terms'!C1:C6,5,FALSE)" In article , "dht" wrote: In VBA I am placing a formula into a cell to copy down. The equation is something like ActiveCell.FormulaR1C1 = "=Vlookup(value(RC[-4]), [tempworkbook]terms!A:F,5,false)" I have specified the other workbook as Tempbook (using Tempbook = ActiveWorkbook.name) as this is an uncontrolled variable. the Excel formula should end up looking like (assuming tempbook is called book9) =Vlookup(Value(A10),[book9]Terms!$A:$F,5,FALSE) I know its something to do with the workbook location (I think) how do I get this to work. |
Referencing another workbook in equation
Have tried that and it doesn't seem to work. I'll have a play around with it
though. Thanks Dave "JE McGimpsey" wrote in message ... Since Tempbook is a variable, which won't be evaluated inside a string, you must concatenate your formula. Also, if you're using R1C1 notation, then A:F won't work. Try: ActiveCell.FormulaR1C1 = "=Vlookup(value(RC[-4]),'[" & _ Tempbook & "]terms'!C1:C6,5,FALSE)" In article , "dht" wrote: In VBA I am placing a formula into a cell to copy down. The equation is something like ActiveCell.FormulaR1C1 = "=Vlookup(value(RC[-4]), [tempworkbook]terms!A:F,5,false)" I have specified the other workbook as Tempbook (using Tempbook = ActiveWorkbook.name) as this is an uncontrolled variable. the Excel formula should end up looking like (assuming tempbook is called book9) =Vlookup(Value(A10),[book9]Terms!$A:$F,5,FALSE) I know its something to do with the workbook location (I think) how do I get this to work. |
All times are GMT +1. The time now is 01:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com