Issue Regarding Application Vlookup

Question

I have two files with different data in it:

1. wbTarget file - the one I start macro in
2. wbSource file - the one I select to find some values in it

Explanation of what I need to achieve:

1. Take first value in wbTarget file column 2
2. Find this value in wbSource file L column
3. If found - take appropriate value from wbSource file A column and put it into wbTarget A column

at the moment I have this code:

Sub knew()


Dim VipFile As String 'the file we choose
Dim wbSource As Workbook 'vip file
Dim wbTarget As Workbook 'this file
Dim Rws As Long, Rng As Range, c As Range
Dim finalrow_A As Integer
Dim finalrow_D As Integer
Dim i As Integer 'counter for values

Application.DisplayAlerts = False 'turn blinking off.

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Select the file you saved as xlsx"
.ButtonName = "Select"
.InitialFileName = "C:\"
If .Show = -1 Then 'ok clicked
VipFile = .SelectedItems(1)

Set wbTarget = ActiveWorkbook
Set wbSource = Workbooks.Open(VipFile)

finalrow_A = wbSource.Sheets(1).Cells(Rows.Count, 12).End(xlUp).Row
finalrow_D = wbTarget.ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

For i = 1 To finalrow_D
    runner = wbTarget.ActiveSheet.Cells(i, 2).Value
    If Not Application.IsError(Application.VLookup(runner, wbSource.Sheets(1).Range("A1:M" & finalrow_A), 12, False)) Then
        wbTarget.Sheets(1).Range("A" & i) = Application.VLookup(runner, _
          wbSource.Sheets(1).Range("A1:M" & finalrow_A), 1, False)
    End If
Next i

wbSource.Close


End If
End With


Application.DisplayAlerts = True 'turn blinking back on.

End Sub

I get an error right here:

finalrow_D = wbTarget.ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

and some mistakes in vlookup part are possible.

Will appreciate your help! Thank you very much!

Answer 1

I suspect your error comes from the fact that one workbook is an xlsx format and the other is xls so the number of rows is different. However, your bigger problem is that Vlookup won't work here since the lookup value is to the right of the value you want to return in your data table. You need to use Match instead:

Sub knew()

    Dim VipFile               As String    'the file we choose
    Dim wbSource              As Workbook    'vip file
    Dim wbTarget              As Workbook    'this file
    Dim Rws As Long, Rng As Range, c As Range
    Dim finalrow_A            As Long
    Dim finalrow_D            As Long
    Dim i                     As Long    'counter for values
    Dim vMatch                As Variant
    Dim runner

    Application.DisplayAlerts = False    'turn blinking off.

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Select the file you saved as xlsx"
        .ButtonName = "Select"
        .InitialFileName = "C:\"
        If .Show = -1 Then    'ok clicked
            VipFile = .SelectedItems(1)

            Set wbTarget = ActiveWorkbook
            Set wbSource = Workbooks.Open(VipFile)

            finalrow_A = wbSource.Sheets(1).Cells(wbSource.Sheets(1).Rows.Count, 12).End(xlUp).Row
            finalrow_D = wbTarget.ActiveSheet.Cells(wbTarget.ActiveSheet.Rows.Count, 2).End(xlUp).Row

            For i = 1 To finalrow_D
                runner = wbTarget.ActiveSheet.Cells(i, 2).Value
                vMatch = Application.Match(runner, wbSource.Sheets(1).Range("L1:L" & finalrow_A), 0)
                If Not IsError(vMatch) Then
                    wbTarget.Sheets(1).Range("A" & i).Value = wbSource.Sheets(1).Range("A" & vMatch)
                End If
            Next i

            wbSource.Close


        End If
    End With


    Application.DisplayAlerts = True    'turn blinking back on.

End Sub