vba 逐行比较Excel中的两列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16949986/
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 Columns in Excel Row By Row?
提问by Anil Lulla
have currently browsed the forums and have came up with a code to compare two columns from two separate excel books and then highlight anything matching with the CompareRange. Here is a few more details about the problem:
目前浏览了论坛并想出了一个代码来比较两本单独的 excel 书中的两列，然后突出显示与 CompareRange 匹配的任何内容。以下是有关该问题的更多详细信息：
I have two excel sheets. And data like this in each sheet:
我有两张excel表。每张表中的数据如下：
(First Sheet) (Second Sheet)
?A B N O ?7 .7 3 .56 ?6 .6 8 .45 ?5 .5 9 .55 ?4 .4 11 .2 ?3 .3 8 .22 ?2 .2 9 .55 ?1 .1 8 .54
?A B N O ?7 .7 3 .56 ?6 .6 8 .45 ?5 .5 9 .55 ?4 .4 11 .2 ?3 .3 8 .22 ?2 .2 9 .55 ?1 .1 8 .54
As you can see, given this example nothing should be highlighted once the macro is run since nothing from Column A or B from the first sheet matches directly with Column N & O from the second sheet. The problem is that with the macro (module) I have come up with will highlight "3" from Column A and ".2" from Column B, just because they appear in Column N & Column O respectivally.
如您所见，鉴于此示例，一旦运行宏，就不应突出显示任何内容，因为第一张表中的 A 列或 B 列中的任何内容都与第二张表中的 N 列和 O 列不直接匹配。问题是，我想出的宏（模块）会突出显示 A 列中的“3”和 B 列中的“.2”，因为它们分别出现在 N 列和 O 列中。
What I want: I only want a number to be highlighted if both the numbers "7" & ".7" are matched in the same row of Column N & Column O on the other spreadsheet. I have no idea how to do this. To be a little more precise, I'll give an example. Say I edited the data to be like this.
我想要什么：如果数字“7”和“.7”在另一个电子表格的第 N 列和 O 列的同一行中匹配，我只希望突出显示一个数字。我不知道该怎么做。为了更准确，我将举一个例子。假设我将数据编辑为这样。
(First Sheet) (Second Sheet)
?A B N O
?7 .7 3 .56
?8 .45 8 .45
?5 .5 9 .55
?11 .4 11 .2
?3 .3 8 .22
?2 .2 9 .55
?1 .1 8 .54
With this data, I would want the second row of A & B ("8" & ".45") highlighted, while my error "3" of Column A and ".2" of Column B is not highlighted. Also, I would like it if row 4 of Column A & B ("11" & ".4") is not highlighted at all either, just because in O it is .2 and in B it would be .4 even though the 11's match.
有了这些数据，我希望 A 和 B 的第二行（“8”和“.45”）突出显示，而 A 列的错误“3”和 B 列的“.2”没有突出显示。另外，如果 A 列和 B 列（“11”和“.4”）的第 4 行根本没有突出显示，我也希望这样做，因为在 O 中它是 0.2，而在 B 中它是 0.4，即使11的比赛。
Please advise. Thanks in advance.
请指教。提前致谢。
Attached is the macro/module I have entered in which is working kind of correctly but producing the mistake.
附件是我输入的宏/模块，它工作正常但产生错误。
And also, (kind of a lesser problem), both the files with data will have the same header, example would be if Column A & Column N both had "Dogs" as it's title in Row 1 and Column B & O both had "Cats" as it's title in Row 1. Is there anyway the macro can be adjusted so it compares those two columns between the two workbooks without me even having to select or assigning a range? Thank you so much.
而且，（一个较小的问题），带有数据的两个文件都将具有相同的标题，例如，如果 A 列和 N 列都具有“狗”，因为它在第 1 行和 B 列和 O 列中的标题都有“ Cats”作为第 1 行的标题。无论如何，宏是否可以调整，以便它比较两个工作簿之间的这两列，而我什至不必选择或分配范围？非常感谢。
Sub Find_Matches()
Dim Column1 As Range
Dim Column2 As Range
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
If Column1.Columns.Count > 1 Then
Do Until Column1.Columns.Count = 1
MsgBox "You can only select 1 column"
Set Column1 = Application.InputBox("Select First Column to Compare", Type:=8)
Loop
End If
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
If Column2.Columns.Count > 1 Then
Do Until Column2.Columns.Count = 1
MsgBox "You can only select 1 column"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
Loop
End If
If Column2.Rows.Count <> Column1.Rows.Count Then
Do Until Column2.Rows.Count = Column1.Rows.Count
MsgBox "The second column must be the same size as the first"
Set Column2 = Application.InputBox("Select Second Column to Compare", Type:=8)
Loop
End If
If Column1.Rows.Count = 65536 Then
Set Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))
Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))
End If
Dim CompareRange As Variant, x As Variant, y As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange = Workbooks("Book4").Worksheets("Sheet1").Range("N2:N7")
Set CompareRange1 = Workbooks("Book4").Worksheets("Sheet1").Range("O2:O7")
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.
' Set CompareRange = Workbooks("Book2"). _
' Worksheets("Sheet2").Range("C1:C5")
'
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.
For Each x In Column1
For Each y In CompareRange
If x = y Then
x.Interior.Color = vbYellow
End If
'x.Offset(0, 5) = x
Next y
Next x
For Each x In Column2
For Each y In CompareRange1
If x = y Then
x.Interior.Color = vbYellow
End If
'x.Offset(0, 5) = x
Next y
Next x
End Sub
结束子
回答by Hyman
Replace both of your loops with one that compares both pairs of cells at the same time:
用同时比较两对单元格的循环替换你的两个循环：
For i = 1 To Column1.Rows.Count
For j = 1 To compareRange.Rows.Count
If Column1.Cells(i, 1) = compareRange.Cells(j, 1) Then
If Column2.Cells(i, 1) = compareRange1.Cells(j, 1) Then
Column1.Cells(i, 1).Interior.Color = vbYellow
Column2.Cells(i, 1).Interior.Color = vbYellow
End If
End If
Next j
Next i
