Excel VBA - Search for value from sheet1 in sheet2 and update with adjacent value from sheet1

Question

I am trying to write some VBA code that will take the first value in a list of unique values in column A on sheet2 and search for it against the unique values in column A on sheet1. Once found, it will then update the cell 6 to the right of the active cell on sheet1 with the value adjacent to the unique code in sheet2. It will continue, running down the rest of the list in sheet2 until it reaches a blank cell.

I've managed to get the code to find the unique values and then update the cell 6 to the right by adding 1, but I can't work out the other bit:

    Private Sub SinglePaste_Click()
    On Error GoTo InvalidBarcode
    Dim EAN As Range
        Sheets("Paste Here").Select
          For Each EAN In ActiveSheet.Range("A:A")
          Sheets("Master Stock File").Select
          With Worksheets("Master Stock File")
          .Range("A:A").Find(What:=EAN, After:=.Range("A1"), LookIn:=xlFormulas, _
          LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False).Activate
        ActiveCell.Offset(0, 6).Select
        ActiveCell.Value = ActiveCell.Value + 1
       End With
      If IsEmpty(EAN) Then Exit For
      Next EAN
     Exit Sub
    InvalidBarcode:
    MsgBox ("Invalid Barcode - " & "" & EAN)
    End Sub

Does this make sense? Any help is greatly appreciated.

So what exactly is "the other bit" that you can't work out? The running until a blank cell or finding the corresponding unique code in the other sheet? Do both sheets have the same unique codes or will some be missing in one sheet? — Ryan

Ryan Ryan · Accepted Answer · 2012-03-02T18:24:16

Your code uses a lot of ActiveSheet, ActiveCell, and Select that is indicative of the Macro Recorder and is generally less efficient. I'm still not sure what "the other bit" you refer to is, but here is a reworked macro:

Public Sub NewSinglePaste_Click()
  Dim EAN As Range
  Dim FoundRange As Range
  Dim ValueCell As Range
  Dim MasterSheet As Worksheet
  Dim PasteSheet As Worksheet

  Set MasterSheet = Sheets("Master Stock File")
  Set PasteSheet = Sheets("Paste Here")

  On Error GoTo InvalidBarcode
  For Each EAN In PasteSheet.Range("A:A")
    If IsEmpty(EAN.Text) Or EAN.Text = "" Then Exit For

    Set FoundRange = MasterSheet.Range("A:A").Find(What:=EAN, _
      After:=MasterSheet.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
      SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    If Not (FoundRange Is Nothing) Then
      Set ValueCell = FoundRange.Offset(0, 6)
      ValueCell.Value = ValueCell.Value + 1
    Else
      Debug.Print "Cell not found: " & EAN.Worksheet.Name & "!" & EAN.Address
    End If
  Next EAN

  Exit Sub

InvalidBarcode:
    MsgBox ("Invalid Barcode - " & "" & EAN)

End Sub

Excel VBA - Search for value from sheet1 in sheet2 and update with adjacent value from sheet1

1 Answers