Loop items in one sheet and match to items in another sheet

[sniper26]

I am trying to loop through the items in SampleData2.xlsx and match them to the items in SampleData1.xlsx. If the item matches, I want to take the price and put it in the Column labeled "Complete". If anyone could offer some guidance, it would save me hours and possibly my sanity. Is this possible to do with excel vba?

SampleData1 - https://ufile.io/7fcf4
SampleData2- https://ufile.io/70896

I am new to excel VBA and only really have the looping of the one sheet working. I'm lost on the comparing/finding on a separate sheet. Any help would be greatly appreciated!

 

[pbornemeier]

I am wary of downloading files. Please use Excel Jeanie or other program mentioned in my sig to capture a few rows for each of your workbooks/worksheets.

This is a generic solution that assumes each worksheet involved from the 2 files is named Sheet1, and the Item ID is in column A, the price in column B

Option Explicit

Sub Main()
    
    Dim sPath As String
    Dim wbk1 As Workbook
    Dim wbkSource As Workbook
    Dim wks1 As Worksheet
    Dim wksSource As Worksheet
    Dim lLastRow As Long
    Dim oFound As Object
    Dim lRowIndex As Long
    Dim vFind As Variant
    
    sPath = ThisWorkbook.Path & "\"
    
    Workbooks.Open Filename:=sPath & "SampleData1.xlsx"
    Workbooks.Open Filename:=sPath & "SampleData2.xlsx"
    
    Set wbk1 = Workbooks("SampleData1.xlsx")
    Set wbkSource = Workbooks("SampleData2.xlsx")
    
    Set wks1 = wbk1.Worksheets("Sheet1")
    Set wksSource = wbkSource.Worksheets("Sheet1")
    
    'For both worksheets the ID is in column A and the price is in column B
    
    'Last row of wks1
    lLastRow = wks1.Cells(wks1.Rows.Count, 1).End(xlUp).Row
    
    With wksSource
        For lRowIndex = 2 To lLastRow       '2 assumes that you have a header row
            vFind = wks1.Cells(lRowIndex, 1).Value
            Set oFound = .Columns("A:A").Find(What:=vFind, LookIn:=xlFormulas, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            If Not oFound Is Nothing Then
                wks1.Cells(lRowIndex, 2).Value = oFound.Offset(0, 1).Value
            Else
                wks1.Cells(lRowIndex, 2).Value = vFind & " not found in workbook: " & wbk1.Name & " Sheet: " & wks1.Name
            End If
    
        Next
    End With
    
    wbkSource.Close savechanges:=False
    wbk1.Close savechanges:=True
    
    Set oFound = Nothing
    Set wks1 = Nothing
    Set wksSource = Nothing
    Set wbk1 = Nothing
    Set wbkSource = Nothing

    
End Sub