比较 VBA 中不同工作表中的 2 个单元格（Excel 2010）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23571730/
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
Compare 2 cells in different sheets in VBA(Excel 2010)
提问by user3465736
Hi Can I ask for a sample macro code to compare 2 different columns from 2 different sheets.
嗨，我可以要求一个示例宏代码来比较 2 个不同工作表中的 2 个不同列吗？
Here's the columnA in sheet1
这是 sheet1 中的 columnA
Here's the column A in sheet2
这是sheet2中的A列
Here's what I need to make as an output in sheet1
这是我需要在 sheet1 中输出的内容
Then all cells in column A sheet1 without match such as red in the picture above should be cut and copied in column C in sheet1 like the below
然后将 A 列 sheet1 中没有匹配项的所有单元格（如上图中的红色）剪切并复制到 sheet1 的 C 列中，如下所示
lastly all cells in column A sheet 2 that has no match should be cut as well and pasted in column D in sheet 1 such as ABC:PINK, ABC:VIOLET and ABC:BLACK as shown below
最后，A 列表 2 中没有匹配项的所有单元格也应剪切并粘贴到表 1 的 D 列中，例如 ABC:PINK、ABC:VIOLET 和 ABC:BLACK，如下所示
Thanks for the help in advance.
我在这里先向您的帮助表示感谢。
Here's what I got so far
这是我到目前为止所得到的
Sub Button1_Click()
On Error GoTo ErrorHndler:
Dim myRange As Range
Dim sRng As Range
Set myRange = Range("A1:A50")
Start:
For Each sRng In myRange
If sRng Like Sheets("Sheet2").Range("A1").Value Then
MsgBox (Sheets("Sheet2").Range("A1").Value) <----it does not pass here
(----I have no Idea what to put here-----)
'GoTo NextCell
Else
'GoTo Start
MsgBox (Sheets("Sheet2").Range("A1").Value)
'MsgBox "Doesn't match" <-----for debugging purposes
End If
NextCell:
Next sRng
ErrorHandler:
MsgBox ""
End Sub
回答by Ripster
You can search a range for a value using Range.Find
您可以使用Range.Find在范围内搜索值
Range.Findreturns Nothingif no match is found or a Rangeif a match is found.
如果未找到匹配项，则Range.Find返回Nothing，如果找到匹配项，则返回Range。
You can check if two objects refer to the same object using the isoperator.
您可以使用is运算符检查两个对象是否引用同一个对象。
Here is an example:
下面是一个例子：
Sub lookup()
Dim TotalRows As Long
Dim rng As Range
Dim i As Long
'Copy lookup values from sheet1 to sheet3
Sheets("Sheet1").Select
TotalRows = ActiveSheet.UsedRange.Rows.Count
Range("A1:A" & TotalRows).Copy Destination:=Sheets("Sheet3").Range("A1")
'Go to the destination sheet
Sheets("Sheet3").Select
For i = 1 To TotalRows
'Search for the value on sheet2
Set rng = Sheets("Sheet2").UsedRange.Find(Cells(i, 1).Value)
'If it is found put its value on the destination sheet
If Not rng Is Nothing Then
Cells(i, 2).Value = rng.Value
End If
Next
End Sub
