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

sniper26

New Member
Joined
Oct 12, 2016
Messages
21
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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,903
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,191,001
Messages
5,984,091
Members
439,872
Latest member
noaman79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top