VBA Code For Vlookup Function with Table_Array from Another Workbook

[Darrellcoholic]

Hi,

I will like to write a VBA code consisting of VLookup function. The problem I face now is that the source of data which i will be pulling the information from is in sheet2 of another workbook and I have tried but failed to come out with a code that work. The code I have so far is as followed. Can someone guide me on how to complete the code to make it work?

Say the path of the source file is this, C:\Users\Ken\Documents\Book 1.xlsx

Please Login or Register  to view this content.

[ffffloyd]

Use single quotes around everything preceding the exclamation mark separator, thus:

.Formula = "=VLOOKUP(A2, '[Book 1]Sheet2'!$A$1:$D$" & Sheets("Sheet2").Range("A" & Rows.Count).End(3)(1).Row & ",3,FALSE)"

The single quotes are only necessary when there is a space or other special characters in the name you are qualifying. The other spreadsheet must also be open at the time and that means you don't have to worry about specifying the full path.

[Darrellcoholic]

Hi ffffloyd,

Thanks for the kind explanation and guidance. However, I made some modification to my code and an error message prompt me that the
"subscript is out of range". The name of my workbook is "May" and the worksheet name is "Expenses" and the workbook are left open. Can
you help to identified where the problem lies with my code?

Please Login or Register  to view this content.

[ffffloyd]

Hi Darrell,

Sorry I have not been back for a while; I have only just seen your reply.

If you haven't already solved this problem, could it be because you are referring to Sheets("Expenses") when Expenses is in that other workbook (May)? You would need to refer to the Sheets array in that workbook, something like: Workbooks("May.xlsm").Sheets("Expenses"). That Rows.Count in the formula looks a little odd too. Does that also need to be the Rows in Workbooks("May.xlsm").Sheets("Expenses")?