Using a Variable as path and filename for lookup formula with VBA

[JAlbers]

Greetings all

I am working on a project that pulls information from several sources and builds a report for the powers that be every week. I actually have it basically working using a pivot table elsewhere but I am not getting all of the records there for some reason and I also wanted to clean up the code a bit and make it more uniform.

The problem I am having is when the code gets to this formula, instead of doing the index(match()) as intended, it is showing the file open box.

Here is the code I have been using

Range("R6:R" & LastRow).Formula = _
        "=IFERROR(INDEX('P:\Product Data\Product Data v1.00.xls'!Inventory_Type,Match($E6" & _
       ",'P:\Product Data\Product Data v1.00.xls'!Item_No,0)),"""")"

Here is the code that doesn't seem to want to work:

Dim wsData As String

wsData = "P:\Product Data\Product Data v1.00.xls"
    
Cells(6, 18).Resize(lastrow - 5, 1).FormulaR1C1 = _
        "=IFERROR(INDEX(" & wsData & "!Inventory_Type," _
            & "MATCH(R[0]C[-13]," & wsData & "!Item_No,0)),"""")"

I have just recently started using the R1C1 method instead of entering the formula and copying down, but this problem seems to be associated with the variable since it never gets to the action of actually entering the formula.

I would appreciate any help or suggestions!

Thanks!

 

[ChrisM]

You get the file open box because Excel is not recognizing the file path/name you have entered.

The format should be:
'FilePath\[Filename.xls]SheetName'!Range

for example:
='x:\database\xyz\[lxx.xlsm]Sheet1'!A1

 

[Smitty]

For a different approach, since you're using Pivot Tables, have you considered PowerPivot? It's great at pulling data from external sources.