VBA - Vlookup not fetching from text lookup value

Question

        Dim sourcewb As Workbook
        Dim targetWorkbook As Workbook
        Dim filter As String
        Dim filter2 As String
        Dim rw As Long
        Dim lookup As String
        Dim X As Range
        Dim y As Range
        Dim a, b As Variant

       Set sourcewb = ActiveWorkbook
        Set X = sourcewb.Worksheets(1).Range("A:G")
        Dim sourceSheet As Worksheet
        Set sourceSheet = sourcewb.Worksheets(1)
        MsgBox sourceSheet.Name
        X.Select

    MsgBox sourcewb.Name

    filter = "(*.xls),*.xls"
    Caption = "Please Select an input file "
    Application.ScreenUpdating = False
    Filename = Application.GetOpenFilename(filter, , Caption)
    Set targetWorkbook = Application.Workbooks.Open(Filename)
    Set y = targetWorkbook.Worksheets(1).Range("A:G")
    y.Select

    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets(1)
    MsgBox targetSheet.Name & " This is the country code sheet name "

    Set targetWorkbook = ActiveWorkbook
    MsgBox targetWorkbook.Name
    y.Select
                              sourcewb.Activate
                        MsgBox ActiveWorkbook.Name & " IS the active workbook"

                        MsgBox sourcewb.Name

                        MsgBox sourcewb.Name & " This is the source workbook "
                        MsgBox targetWorkbook.Name & " This is the target workbook "
                        MsgBox "Trying to map from target to source "



                        With sourcewb.Worksheets(1)
                        For rw= 2 To Cells(Rows.Count, 1).End(xlUp).Row

                             Cells(rw, 4) = Application.VLookup(Cells(rw, 1).Value, y, 4, False)
                             'MsgBox Cells(a, 4).Value2
                              Next rw
                        End With


                        MsgBox "All required columns from source mapped to target file "
                        Set sourcewb = ActiveWorkbook
                        MsgBox ActiveWorkbook.Name
                        Application.ScreenUpdating = False

I have a workbook sourcewb. I open another workbook targetworkbook from the sourceworkbook. My Columns in sourcewb are Sl No, Country code,country names

slno           country code      country name                Region     
  1               AL               Algeria                    
  2               US               USA                        
  3               UK               United Kingdom             

My targetwb is

         country code      country name                Region     
               AL               Algeria                   EMEA    
               US               USA                       Americas   
               UK               United Kingdom            Europe  

I am trying to fetch Region column from country code in the sourcewb as there is no slno in the targetwb and the order of country codes are not the same as sourcewb.

I get an error 2042. I have tried storing the target value with string, int, long, variant, nothing has worked till now.

Any suggestions or help would be really helpful.

Answer 1

With some "clean-up" and organization to your original code, try the code below.

3 comments:

1. When you are using a With statement, don't forget to nest all objects inside with a ..
2. Stay away from using Select and Activate, not only it's not necessary, it also slows down your code's run-time.
3. You need to trap the scenario that Application.VLookup will not find a value, and then you will get a run-time error.

Explanations inside the code as comments.

Code

Option Explicit

Sub AutoVLookup()

Dim sourcewb As Workbook
Dim targetWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim X As Range
Dim y As Range

Dim filter As String
Dim filter2 As String
Dim rw As Long
Dim lookup As String
Dim a, b As Variant

Set sourcewb = ActiveWorkbook ' set Activeworkbook object
Set sourceSheet = sourcewb.Worksheets(1) ' set source sheet
Set X = sourceSheet.Range("A:G") ' set source range

filter = "(*.xls),*.xls"
Caption = "Please Select an input file "
Application.ScreenUpdating = False
Filename = Application.GetOpenFilename(filter, , Caption)

Set targetWorkbook = Workbooks.Open(Filename) ' set target workbook object
Set targetSheet = targetWorkbook.Worksheets(1) ' set target sheet
Set y = targetSheet.Range("A:G") ' set target range

With sourceSheet
    For rw = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row ' get last row in column A
        ' make sure VLoookup found a match, otherwise you will get a run-time error
        If Not IsError(Application.VLookup(.Cells(rw, 1).Value, y, 4, False)) Then
            .Cells(rw, 4) = Application.VLookup(.Cells(rw, 1).Value, y, 4, False) ' this will fetch column "E" values
            'MsgBox Cells(a, 4).Value2
        End If
    Next rw
End With

MsgBox "All required columns from source mapped to target file "

Application.ScreenUpdating = True

End Sub