Find one value with another workbook and copy correspondent value

[NILUSHA MADUMAL]

Hi,There are two workbooks which I refer as workbook 1 and workbook 2.What I need to get done is there are some values in column "B" of workbook 1,and I need to find these values from column "I" of workbook 2 and copy correspondent value of column "K" of workbook 2 and paste it to the column "C" of workbook 01.Below code does not respond me when I run the macro.How do I solve this ?where have I done wrong?

Option Explicit

Public Sub FindAndCopy()


    Const B = "B"
    Const I = "I"
    Const C = 2
    Const K = 15


    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr1 As Long
    Dim lr2 As Long
    
    Set ws1 = Workbooks("AAA.xlsx").Worksheets("Sheet1")
    Set ws2 = Workbooks("BBB.xlsx").Worksheets("Sheet1")
    lr1 = ws1.Cells(ws1.Rows.Count, B).End(xlUp).Row
    lr2 = ws2.Cells(ws2.Rows.Count, I).End(xlUp).Row


    Dim itm1 As Range, itm2 As Range


    Application.ScreenUpdating = False
    For Each itm2 In ws2.Range(ws2.Cells(1, I), ws2.Cells(lr2, I))      'workbook 2
    For Each itm1 In ws1.Range(ws1.Cells(1, B), ws1.Cells(lr1, B))  'workbook 1
    If Not IsError(itm1) And Not IsError(itm2) Then
    If InStr(1, itm2.Value2, itm1.Value2) > 0 Then
    itm1.Offset(, C).Formula = itm2.Offset(, K).Formula 'Here.C = workbookBook2.K
                    Exit For
                End If
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

 

[ravishankar]

try the following macro codes

Sub nilusha()
Dim b As String
Dim a As Integer, x As Integer, y As Integer
x = Workbooks("AAA.xlsx").Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
y = Workbooks("BBB.xlsx").Worksheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row
    For a = 2 To x
    b = Workbooks("AAA.xlsx").Worksheets("Sheet1").Cells(a, 2)
    Cells(a, 3) = WorksheetFunction.VLookup(b, Workbooks("BBB.xlsx").Worksheets("Sheet1").Range("I2:k" & y), 3, False)
    Next a
MsgBox "complete"
End Sub

ravishankar

 

[NILUSHA MADUMAL]

@ravishankar, I would really appreciate your assistance given to get solved my problem but unfortunately when I run the code it displays me an error message "Unable to get the VLoook up property of the worksheetFunction class".How do I solve this?