Selective Search and Transfer

[dccjr3927]

I have two sheets, one the master with a UPC number column and a Product ID column. The other has only the UPC and inventory information. Not all rows have a product ID in the first sheet. I would like to have a script or macro go down the 2nd sheet at each UPC and see in a product ID is in the first sheet. If so, I would like to have in write the Product ID into the same row and a different column.

Any help would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
A couple of questions
1) What are the sheet names?
2) What columns do we need to look at?
3) Do you have headers in row 1 with data starting in row 2?

 

[dccjr3927]

1.) The first sheet is Master Stock, the second is Inventory Count.
2.) On Master Stock, Column A is the UPC and Column H is the Product ID. On Inventory Count, Column A is the UPC.
3.) Master Stock has headers in Row 1, data starting in Row 2, In Inventory Count, data starts in Row 4.

 

[dccjr3927]

Product ID added to Inventory Count will go into Column F.

 

[Fluff]

Ok, try this

Sub dccjr3927()
   Dim Cl As Range
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Set Ws1 = Sheets("Master Stock")
   Set Ws2 = Sheets("Inventory Count")
   Application.ScreenUpdating = False
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, 7).Value
      Next Cl
      For Each Cl In Ws2.Range("A4", Ws2.Range("A" & Rows.Count).End(xlUp))
         Cl.Offset(, 5).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[dccjr3927]

I apologize for the delay in saying thank you for all of your help. It works perfectly.

 

[Fluff]

Glad to help & thanks for the feedback