VBA - VLookup Across Workbooks

[ryplew]

Sub VLookup()

Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row

Dim Desc_Row As Long
Dim Desc_Clm As Long
Dim lastSourceColumn As Integer
Dim lastSourceRow As Long

Workbooks("Generate Vendor Doc for Item Submission.xlsm").Sheets("Sheet1").Activate
'''Get last row and last column
    If WorksheetFunction.CountA(Cells) > 0 Then
        lastSourceRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lastSourceColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If

'''VLookup
Table1 = Workbooks("D266 BLANK ORDER INFO WORKSHEET.xlsx").Sheets("ISU").Range("A6:A" & lastRow)
Table2 = Workbooks("Generate Vendor Doc for Item Submission.xlsm").Sheets("Sheet1").Range(Cells(1, 1), Cells(lastSourceRow, lastSourceColumn))
Desc_Row = Workbooks("D266 BLANK ORDER INFO WORKSHEET.xlsx").Sheets("ISU").Range("C6").Row
Desc_Clm = Workbooks("D266 BLANK ORDER INFO WORKSHEET.xlsx").Sheets("ISU").Range("C6").Column

For Each cl In Table1
    Workbooks("D266 BLANK ORDER INFO WORKSHEET.xlsx").Sheets("ISU").Cells(Desc_Row, Desc_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 3, False)
    Desc_Row = Desc_Row + 1
Next cl

End Sub

D266 BLANK ORDER INFO WORKSHEET.xlsx contains the lookup value and result destination.
Generate Vendor Doc for Item Submission.xlsm contains the code and table array.

Above is a code found online, modified to suit my needs. However, I am getting a type mismatch error on the following line:

Workbooks("D266 BLANK ORDER INFO WORKSHEET.xlsx").Sheets("ISU").Cells(Desc_Row, Desc_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 3, False)

More specifically with:

Application.WorksheetFunction.VLookup(cl, Table2, 3, False)

If I replace this portion with a test text (e.g. "TEST"), it works...somewhat. It fills down the the correct column with "TEST", yet I would have expected to only fill down the amount of rows that I have lookup values for. Instead, it filled down an amount of rows equal to the total number of rows in the table array.

Also, I am noticing that cl is neither DIM'd nor defined. Should it be?

Any help is greatly appreciated! My apologies up front if I have not explained anything clearly enough.

SAMPLE FILES:
[link ]

http://files.engineering.com/getfil...=Generate_Vendor_Doc_for_Item_Submission.xlsm

[/url]
[link ]

http://files.engineering.com/getfil...97e&file=D266_BLANK_ORDER_INFO_WORKSHEET.xlsx

[/url]

 

[SkipVought]

Hi,

First, I would not use any lookup across multiple sheets/workbooks. It's not the best tool in the toolbox.

Second, having similar data in multiple locations (sheets/workbooks) is a very poor design for an operating system. Excel works best using best and accepted table design. The design you have is sub-optimal non-normalized tables. It a recipe for disaster.

You'd be better off using code to consolidate your data into a single table, from which any lookup could simply be accomplished.

BTW, your Table1 & Table2 should be 1) declared as Range and 2) assigned in a Set statement as...

Dim RangeVar As Range
'....
Set RangeVar = SomeRangeStatement

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]