vba 用于比较两列以查找匹配值的 Excel 2010 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27700515/
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 2010 Macro to compare two columns for finding the matched value
提问by Hardworker
I am very new to Excel macros and VBA, kindly help me pleasewith my below situation. Here is the situation, i have two sheets (sheet1 and sheet 2) in sheet1 there is two columns name and number and in sheet2 i have numbers along its other information like date, charging and etc.
我很新的Excel宏和VBA，请帮我请与我下面的情况。这是这种情况，我在工作表 1 中有两张工作表（工作表 1 和工作表 2），有两列名称和编号，在工作表 2 中，我有其他信息（如日期、收费等）的编号。
Sheet 1
第 1 页
No Name PhoneNumber
无姓名电话号码
1 Bob 7254
1 鲍勃 7254
2 Cristin 5468
2 克里斯汀 5468
3 Luara 1234
3 卢阿拉 1234
Sheet2
表 2
No PhoneNumber Date Charged Name
没有电话号码 日期 收费姓名
1 1145 12/30/2014 2$
1 1145 12/30/2014 2$
2 7254 11/26/2014 3$
2 7254 11/26/2014 3$
3 2365 3/9/2014 7$
3 2365 3/9/2014 7$
4 5468 3/10/2014
4 5468 3/10/2014
5 1234 3/11/2014
5 1234 3/11/2014
What i want is to compare PhoneNumber column of sheet2 (B column) with PhoneNumber column of sheet1 (C column) and if a matching is found then copy Name (B column) from Sheet1 into Name column of sheet2 (E column). If no match then the name column in sheet2 must be blank.
我想要的是将 sheet2 的 PhoneNumber 列（B 列）与 sheet1 的 PhoneNumber 列（C 列）进行比较，如果找到匹配项，则将 Sheet1 中的 Name（B 列）复制到 sheet2 的 Name 列（E 列）中。如果没有匹配项，那么 sheet2 中的 name 列必须为空。
i have searched and found the below code and modified a bit but i am not sure whether it is correct or no:
我搜索并找到了以下代码并进行了一些修改，但我不确定它是否正确：
Sub test()
Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Sheet2").Range("B" & i)
For j = 1 To Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Sheet1").Range("C" & j)
If rng1.Value = rng2.Value Then
Range("B2:B" & TotalRows).Copy Destination:=Sheets("Sheet2").Range("E2")
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next i
End Sub
Please help me as the time is so short for my this project and i will highly appreciate your assistance in this regard.
请帮助我，因为我的这个项目时间很短，我将非常感谢您在这方面的帮助。
回答by Edward Bagby
Looks like you were almost there. Your copy line needed a little tweaking, however. In the example below, I added an additional variable called rngName, to store the range of the name to be copied and assigned it a value in the for j loop. if the numbers match (i.e. rng1.value = rng2.value) it will copy the range containing the name to the associated row in sheet 2. Notice that I used .Range ("E" & i) for the copy-to range. The copy-to range in your example would always drop the name in the same cell as its always assigned to "E2". Also, you have a variable called TotalRows that had no value. It must have meant something in the original code you copied it from, so I got rid of that too. Try this and let me know how it works for you.
看起来你快到了。但是，您的复制行需要稍作调整。在下面的示例中，我添加了一个名为 rngName 的附加变量，用于存储要复制的名称范围并在 for j 循环中为其分配一个值。如果数字匹配（即 rng1.value = rng2.value），它会将包含名称的范围复制到工作表 2 中的关联行。请注意，我使用了 .Range ("E" & i) 作为复制范围。您示例中的复制到范围将始终将名称放在与其始终分配给“E2”的同一单元格中。此外，您有一个名为 TotalRows 的变量，它没有任何值。它一定在你复制它的原始代码中意味着什么，所以我也摆脱了它。试试这个，让我知道它是如何为你工作的。
Sub test()
Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer
For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Sheet2").Range("B" & i)
For j = 1 To Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Sheet1").Range("C" & j)
Set rngName = Sheets("Sheet1").Range("B" & j)
If rng1.Value = rng2.Value Then
rngName.Copy Destination:=Worksheets("Sheet2").Range("E" & i)
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next i
End Sub
