I often have problem like this especially when you are using the single
quotes. You have spaces in the file name which require the single quotes.

I would build my formula string in pieces as follows

Myformula = "=VLOOKUP(RC[-13],'[" & currentfile & "SAP'!R2C1:R509C1,1,0)"
ActiveCell.FormulaR1C1 = Myformula

"Azin05" wrote:

Hi Y' all :)

I'm currently trying to write a macro that will fill a range of cells with
the same vlookup-formula. results should be visible in the sheet. The problem
however is that the worksheet in which the formula needs to lookup in ( the
table array) will vary daily. to solve this obstacle i created the following:

Dim currentfile As String
currentfile = ActiveWorkbook.Name
Workbooks.Open Filename:="S:\...\SAP Data\download.xls"

(the macro itself is stored in the file in which it needs to look and is
therefor already open and active( before the download.xls file is opened))

The vlookupfunction is as follows:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[planning pharma v1.005
11-07-07_macrobouw.xls]SAP'!R2C1:R509C1,1,0)"

and its the [planning pharma v1.005 11-07-07_macrobouw.xls] that should be
replaced with the currentfile dimension
so far I've tried:
'[&currentfile&]
'[&currentfile]
'[currentfile]
'["&currentfile]

but all of these give either an error in VBA or when F8-ing myself through
it the macro want me to locate the file.

any body got a clue on how I can integrate the dim into the formula?

2nd question concers the R2C1:R509C1 section.
this table will not be limited to the current number of rows but wil expand
and therefor should be a flexible array. ( just stating R32000 is a but crude
solution, but it would work) I'm a bit lost here as well, any one got an idea?

Many thanks in advance

Azin05