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

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.

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.