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