vba 比较两个不同的 Excel 工作表和工作簿中的列，然后将匹配的值发布到另一列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17882516/
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 columns in two different excel sheets and workbooks, then posting the matching value into another column
提问by Jake Ison
I'm having lots of trouble trying to figure out this macro, so right now I'm using 2 sheets in a workbook; I'd like to use two different workbooks but that isn't as important as the issue.
我在试图找出这个宏时遇到了很多麻烦，所以现在我在工作簿中使用了 2 张纸；我想使用两个不同的工作簿，但这并不像问题那么重要。
To explain, Sheet1 column E is the id number and in Sheet2 column A is the id number, now if any value in Sheet1 E matches a value in column A in Sheet2, I need the macro to copy the corresponding cell from Sheet 2 column D. So essentially if E5 (from sheet 1) matched A1 in Sheet2, I would need the macro to output Sheet2's D1 to Sheet1's F5. It would also be nice to delete the values of Sheet1 column F if Sheet1 column E doesn't match a value in Sheet 2 column A.
为了解释一下，Sheet1 列 E 是 id 号，而 Sheet2 中的列 A 是 id 号，现在如果 Sheet1 E 中的任何值与 Sheet2 中 A 列中的值匹配，我需要宏从 Sheet2 列 D 中复制相应的单元格. 所以基本上如果 E5（来自工作表 1）与工作表 2 中的 A1 匹配，我将需要宏将工作表 2 的 D1 输出到工作表 1 的 F5。如果 Sheet1 列 E 与 Sheet 2 列 A 中的值不匹配，则删除 Sheet1 列 F 的值也会很好。
I have some code but it just pastes the values from Sheet2's column D if a value of Sheet1 column E matches a value from Sheet2 column A. The problem is when the values pasted from Sheet2 are pasted in Sheet1's column F and the values aren't matched with the correct value that it matched with in Sheet2. They are just dumbed. So if Sheet1 column E was like this
我有一些代码，但如果 Sheet1 列 E 的值与 Sheet2 列 A 中的值匹配，它只是粘贴来自 Sheet2 的 D 列的值。问题是当从 Sheet2 粘贴的值粘贴到 Sheet1 的 F 列中时与它在 Sheet2 中匹配的正确值匹配。他们只是傻了。所以如果 Sheet1 列 E 是这样的
Sheet1 Column E Sheet1 F
1317 relays_120x120.jpg
1319 Control%20boards_120x120
1320 Control%20boards_120x120
Sheet2 Column A Sheet2 column D
1317 relays_120x120
1318 /relays_120x120
1319 ebay/SingleRunOval
But in reality I need them all to be equal and if Sheet1 column E has a value that isnt in Sheet2 column, then dont post a link in Sheet1 column F leave it blank.
但实际上我需要它们都相等，如果 Sheet1 列 E 的值不在 Sheet2 列中，则不要在 Sheet1 列 F 中发布链接，将其留空。
Here is the code I have
这是我的代码
Sub FindMatches()
Dim oldRow As Integer
Dim newRow As Integer
Dim i As Integer
i = 1
For oldRow = 2 To 1170
For newRow = 1 To 1170
If Worksheets("Sheet1").Cells(oldRow, 5) = Worksheets("Sheet2").Cells(newRow, 1) Then
Worksheets("Sheet1").Cells(i, 6) = Worksheets("Sheet2").Cells(oldRow, 4)
i = i + 1
Exit For
End If
Next newRow
Next oldRow
End Sub
回答by ARich
Sounds like you could accomplish your goal with the VLookup function. Add this formula to Sheet1 F1: =IFERROR(VLookup(E1,Sheet2!A:D,4,FALSE),"")
听起来您可以使用 VLookup 功能实现您的目标。将此公式添加到 Sheet1 F1：=IFERROR(VLookup(E1,Sheet2!A:D,4,FALSE),"")
That formula will copy the cell into sheet1 if a match is found, but if no match is found the cell will remain blank.
如果找到匹配项，该公式会将单元格复制到 sheet1，但如果未找到匹配项，该单元格将保持空白。
回答by Sree
Try to use the below code. I have just modified your code using StrComp function
尝试使用下面的代码。我刚刚使用 StrComp 函数修改了您的代码
Sub FindMatches()
Dim oldRow As Integer
Dim newRow As Integer
Dim i As Integer
i = 1
For oldRow = 1 To 1170
For newRow = 1 To 1170
If StrComp((Worksheets("Sheet1").Cells(oldRow, 5).Text), (Worksheets("Sheet2").Cells(newRow, 1).Text), vbTextCompare) <> 0 Then
i = oldRow
Worksheets("Sheet1").Cells(i, 6) = " "
Else
Worksheets("Sheet1").Cells(i, 6) = Worksheets("Sheet2").Cells(newRow, 4)
i = i + 1
Exit For
End If
Next newRow
Next oldRow
End Sub
