Excel VBA-在sheet2中的sheet1中搜索值，并使用sheet1中的相邻值进行更新 [英] Excel VBA - Search for value from sheet1 in sheet2 and update with adjacent value from sheet1

问题描述

我正在尝试编写一些VBA代码，该代码将采用sheet2上A列的唯一值列表中的第一个值，并针对sheet1上A列的唯一值进行搜索.一旦找到，它将使用与sheet2中唯一代码相邻的值来更新sheet1上活动单元格右侧的单元格6.它将继续进行，在sheet2中的其余列表上运行，直到到达空白单元格为止.

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.

我设法获得了查找唯一值的代码，然后通过添加1将单元格6更新到右侧，但是我无法计算出另一位:

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.

推荐答案

您的代码使用了大量的ActiveSheet，ActiveCell和Select，它们表示宏记录器，通常效率较低.我仍然不确定您指的是另一位"，但这是一个经过重做的宏:

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-在sheet2中的sheet1中搜索值，并使用sheet1中的相邻值进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！