Using filename in VLOOKUP

[Ricardo Mass]

Hi everybody,

I am trying to enter a vlookup to a closed file, and to save me changing the path name in the code each month would like to refer to it using a string.

I have tried:

Public PreviousMonth As String
PreviousMonth = "F:\Global Finance\Financial Reporting\Financial Reports\02 May 13\Data & Other Downloads\Debtors\[DEBTORS 31.05.13 Final.xlsx]DEBTORS 31.05.13"

Range(Cells(2, 75), Cells(ODRows, 75)).Formula = "=IFERROR(VLOOKUP(A:A,PreviousMonth!$A:$CD,48,0),""NEW"")"

...but I think there is a problem with my syntax.

Does anyone have any suggestions?

Also, as there are about 200k lookups to do this really slows the code up, is there a better way to apply a vlookup in vba?

Many thanks

Rich

[Norie]

Rich

Wouldn't it be easier/quicker to have code that opens the workbook and retrieves the values you want?

[Ricardo Mass]

Thanks Norie, I'm still trying to figure that one out, using concatenation to update the name of the file to be opened.

Would you still use a vlookup when you've opened the file?

- I tried to respond on Friday but the messages weren't going through.