vba 比较两张表 - 将差异写入表 3
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27499092/
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
Comparing two sheets - difference write to sheet 3
提问by DRastislav
here is the thing: i have 2 Sheets (approx 10k rows in both) - i would like to compare them - if there is match (based on column B - do nothing, if there isn′t - value is in sheet2 but not in sheet 1 - write all columns(A,B,C,D) to sheet3) - Problem is, that first row in sheet 1 is equal maybe with 3rd row in sheet2.
事情是这样的：我有 2 张表（两者都有大约 10k 行） - 我想比较它们 - 如果有匹配（基于 B 列 - 什么都不做，如果没有 - 值在 sheet2 中但不在表 1 - 将所有列（A、B、C、D）写入表 3） - 问题是，表 1 中的第一行可能与表 2 中的第三行相等。
How can i achieve that?
我怎样才能做到这一点？
Is it possible to check it row by row according to values in column B?
是否可以根据 B 列中的值逐行检查它？
Thanks
谢谢
回答by Ahmad
I have created a workbook with the following criteria:
我创建了一个具有以下条件的工作簿：
Sheet1:
表 1：
Column A | Column B | Column C | Column D | Column E
------------------------------------------------------
111024 961207 value1 data a fake 11
111027 961209 value2 data b fake 22
111030 961211 value3 data a fake 33
...
...
...
and sheet 2 is a copy of sheet 1 but with a couple of rows missing.
表 2 是表 1 的副本，但缺少几行。
Then I opened Visual Basic Editor (Alt+F11) and added a module, then wrote the following macro:
然后我打开 Visual Basic Editor ( Alt+ F11) 并添加了一个模块，然后编写了以下宏：
Sub compare()
Sheets(3).Activate 'Go to sheet 3
Cells.Clear 'and clear all previous results
Range("a1").Select 'set cursor at the top
Sheets(1).Activate 'go to sheet 1
Range("a1").Select 'begin at the top
Dim search_for As String 'temp variable to hold what we need to look for
Dim cnt As Integer 'optional counter to find out how many rows we found
Do While ActiveCell.Value <> "" 'repeat the follwoing loop until it reaches a blank row
search_for = ActiveCell.Offset(0, 1).Value 'get a hold of the value in column B
Sheets(2).Activate 'go to sheet(2)
On Error Resume Next 'incase what we search for is not found, no errors will stop the macro
Range("b:b").Find(search_for).Select 'find the value in column B of sheet 2
If Err <> 0 Then 'If the value was not found, Err will not be zero
On Error GoTo 0 'clearing the error code
Sheets(1).Activate 'go back to sheet 1
r = ActiveCell.Row 'get a hold of current row index
Range(r & ":" & r).Select 'select the whole row
cnt = cnt + 1 'increment the counter
Selection.Copy 'copy current selection
Sheets(3).Activate 'go to sheet 3
ActiveCell.PasteSpecial xlPasteAll 'Past the entire row to sheet 3
ActiveCell.Offset(1, 0).Select 'go down one row to prepare for next row.
End If
Sheets(1).Activate 'return to sheet 1
ActiveCell.Offset(1, 0).Select 'go to the next row
Loop 'repeat
Sheets(3).Activate 'go to sheet 3 to examine findings
MsgBox "I have found " & cnt & " rows that did not exist in sheet 2"
End Sub
Then I ran the macro and found it is working..
然后我运行了宏，发现它正在工作..
I hope this answer helped you achieve what you want.
我希望这个答案能帮助你实现你想要的。
If you want, here is the Excel workbook I createdYou will need to Enable macro before you could see the code run. Office will automatically warn you of any Excel file that contain macros.
如果需要，这里是我创建的 Excel 工作簿您需要先启用宏，然后才能看到代码运行。Office 将自动警告您任何包含宏的 Excel 文件。
