Referencing another workbook in equation

  • Thread starter Thread starter dht
  • Start date Start date
D

dht

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)"
 
Have tried that and it doesn't seem to work. I'll have a play around with it
though.

Thanks
Dave

JE McGimpsey said:
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)"

dht said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

unprotecting sheets in another workbook (syntax problem?) 1
Referencing a workbook in a lookup 1
Referencing Defined Name in another workbook 2
Match and VLOOKUP from open workbook, storing values in Active wkb 1
Using cell name to open workbook 1
macro to copy rows that contain a cell with a certain number 2
Referencing another workbook in a VLookUp 2
cell ref in a formula 1

Back
Top