Excel VBA - 在工作表 2 中的工作表 1 中搜索值并使用工作表 1 中的相邻值进行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9537816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Search for value from sheet1 in sheet2 and update with adjacent value from sheet1
提问by Nik Blumer
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.
我正在尝试编写一些 VBA 代码，该代码将采用 sheet2 上 A 列中唯一值列表中的第一个值，并根据 sheet1 上 A 列中的唯一值搜索它。找到后，它将使用与 sheet2 中唯一代码相邻的值更新 sheet1 上活动单元格右侧的单元格 6。它将继续运行，沿着 sheet2 中的列表的其余部分运行，直到到达一个空白单元格。
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:
我设法让代码找到唯一值，然后通过添加 1 将单元格 6 更新到右侧，但我无法计算出另一位：
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.
这有意义吗？任何帮助是极大的赞赏。
回答by Ryan
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:
您的代码使用了大量 ActiveSheet、ActiveCell 和 Select，它们表示宏记录器，通常效率较低。我仍然不确定您所指的“另一位”是什么，但这是一个重新设计的宏：
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
