Why do I get #N/A's when using VLookup in VBA to reference another Excel file?

0
votes

I'm trying to program a VLookup in VBA that references another workbook. Basically I'm trying to vlookup across 2 workbooks.

Here is a simple outline of my goal:

  • Look up values in all rows in column A of workbook1
  • Pull the information in from column A (values that correspond to Column A in workbook1) and Column C (values to be copied) of workbook2 and place in column B of workbook1 in the corresponding cells (rows). Additional conditions: (1) if there are already values in the cells, overwrite and (2) if no value has been found for a cell, then keep cell unchanged. So also no #N/A.
  • Move on and repeat the process until there are no more entries left in column A of workbook1

Below is the code that I already have. I stumble upon the fact that the code does not work. I keep getting #N/As for each row in column B, while there should at least be some values found and pasted into some cells. For testing purposes, I copied the reference table in workbook2 to a worksheet in workbook1 and when I reference to that specific worksheet in the same workbook1 then the code seems to work.

Sub Import()

Dim rw As Long, x As Range
Dim extwbk As Workbook, twb As Workbook

Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("C:\Documents\workbook2.csv")
Set x = twb.Worksheets("Sheet1").Range("A1:H100")

With twb.Sheets("Sheet1")

     For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
         .Cells(rw,2) = Application.Vlookup(.Cells(rw,1).Value2,x,3,False)
     Next rw

End With


End Sub

Do you see an error in my code? Or does anyone have a better way of accomplishing this task? Preferably, I also would like to open a selected file instead of referring to a full path.

Thanks in advance!

2
excelvbaexcel-formulavlookup
Please, try formatting both ranges which are compared in the same way. The best formatting, in such a case, would be to use TextToColumns (from Data)... - FaneDuru
@FaneDuru while the extensions differ, the formatting is the same - Jonathan

2 Answers

1
votes

Maybe try this:

    For ...    
         on Error Resume Next
         .Cells(rw, 2).Value = WorksheetFunction.Vlookup(.Cells(rw,1).Value, x, 3, False))
    Next ...

But if I got you right "x" is the range where you search for a value AND it is defined as a range on the same sheet. So the ranges overlap!?

0
votes

Possibly because this line:

Set x = twb.Worksheets("Sheet1").Range("A1:H100")

is referencing the wrong workbook. It should be:

Set x = extwbk.Worksheets("Sheet1").Range("A1:H100")

Handling N/A Values Try code like this in your loop:

For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
        If Application.IsNA(Application.VLookup(.Cells(rw, 1).Value2, x, 3, False)) Then
            .Cells(rw, 2) = "NOT FOUND"
        Else
            .Cells(rw, 2) = Application.VLookup(.Cells(rw, 1).Value2, x, 3, False)
        End If
         
     Next rw

Replace "NOT FOUND" with whatever you want displayed, or just "" if you want an empty cell