Referencing a workbook in a lookup
 

Hi

I have a macro which trails through x number of spreadsheets and updates
the current months values into a data book which is then linked to an output.

In the control sheet of my workbook i have a looping function which iterates
through each of the workbooks - however in the vlookup I am having difficulty
referencing the relevant workbook as a variable

The formula is as

Windows("MyWorkBooks").Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],"REPORTNAME"! R33C3:R84C12,10,0)"

Where

Dim ReportName As String
ReportName = xxxxx.Value


Any suggestions as to what I am doing wrong ?

Many thanks in advance

Hardeep

Referencing a workbook in a lookup
 

Maybe...

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],"REPORTNAME!R33C3:R84C12",10,0)"

But I'm not sure how the different workbooks enter the picture.

If you need different workbooks, you can build your formula manually (with the
sending workbook open) and then close that sending workbook.

Switch to R1C1 reference style and look at the formula. Then use that to build
the VBA code.

Spinz wrote:

Hi

I have a macro which trails through x number of spreadsheets and updates
the current months values into a data book which is then linked to an output.

In the control sheet of my workbook i have a looping function which iterates
through each of the workbooks - however in the vlookup I am having difficulty
referencing the relevant workbook as a variable

The formula is as

Windows("MyWorkBooks").Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],"REPORTNAME"! R33C3:R84C12,10,0)"

Where

Dim ReportName As String
ReportName = xxxxx.Value

Any suggestions as to what I am doing wrong ?

Many thanks in advance

Hardeep

--

Dave Peterson