使用 VBA 检查两个不同的工作表中是否存在单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7419018/
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
Check if cell value exists in two different sheets using VBA
提问by Eric
I have two different sheets that contain 500+ names on them. I need to see if the name on one sheet exists on the other. and if it does say yes or no in the specified column.
我有两张不同的工作表，上面有 500 多个名字。我需要看看一张纸上的名字是否存在于另一张纸上。如果它在指定的列中说是或否。
The name would be something like ASmith in one sheet, and then ASMITH in the other sheet so case sensitivity is not required.
该名称在一张表中类似于 ASmith，然后在另一张表中是 ASMITH，因此不需要区分大小写。
i'm so confused on the VBA part. I program in java and to do this sort of thing in java is easy to me. But in excel, not so much. your help is appreciated. TIA
我对 VBA 部分很困惑。我用java编程，用java做这种事情对我来说很容易。但在excel中，没有那么多。感谢您的帮助。TIA
回答by Ed Bolton
Firstly, Visual Basic does care about case sensitivity so when comparing the two sheets, you will need to convert everything to upper case using VBA.UCase. I have also included the Trim function as there may be spaces around the word itself. Any further text manipulations required to create two comparable strings will have to be researched. I would recommend this expert guidanceon manipulating text strings with Excel macros
首先，Visual Basic 确实关心区分大小写，因此在比较两个工作表时，您需要使用 VBA.UCase 将所有内容转换为大写。我还包括了 Trim 函数，因为单词本身周围可能有空格。必须研究创建两个可比较字符串所需的任何进一步文本操作。我会推荐这个关于使用 Excel 宏操作文本字符串的专家指南
So...assuming both the sheets are within the same workbook, the best starting point would be to get the codenames of the two sheets from within Visual Basic Editor (by default, Sheet1/Sheet2 etc.). In the below, I have assumed Sheet1 is the Output sheet, and Sheet2 is the data you are searching. If you can't fine code names, you can substitute Sheets("SheetName1") and Sheets("SheetName2") in the below code:
所以...假设两个工作表都在同一个工作簿中，最好的起点是从 Visual Basic 编辑器中获取两个工作表的代号（默认情况下，Sheet1/Sheet2 等）。在下面，我假设 Sheet1 是输出表，而 Sheet2 是您正在搜索的数据。如果你不能定义代码名称，你可以在下面的代码中替换 Sheets("SheetName1") 和 Sheets("SheetName2")：
Dim SourceRow as Long
Dim DestRow as Long
For DestRow = 1 to 10
For SourceRow = 1 to 10
If VBA.Trim(VBA.UCase(Sheet1.Cells(DestRow,"DestCol"))) = VBA.Trim(VBA.UCase(Sheet2.Cells(SourceRow,"SourceCol"))) then
Sheet1.Cells(DestRow,"OutputCol") = "Yes"
Goto NextDestRow
End If
Next SourceRow
Sheet1.Cells(DestRow,"OutputCol") = "No"
NextDestRow:
Next DestRow
The code assumes that you are searching for data in a single column in each sheet and where I've written "DestCol" and "SourceCol", you should simply insert the numeric value of that column (where A=1, B=2 etc.). "OutputCol" is the column in which you want to place the output on the destination sheet
该代码假定您正在每个工作表中的单个列中搜索数据，并且我在其中编写了“DestCol”和“SourceCol”，您应该简单地插入该列的数值（其中 A=1、B=2 等.) “OutputCol”是您想要在目标工作表上放置输出的列
The loop assumes you are interested in Rows 1 to 10 on each sheet; adjust as necessary
该循环假设您对每张纸上的第 1 行到第 10 行感兴趣；根据需要调整
回答by Reafidy
Try something like this:
尝试这样的事情：
Sub HTH()
With Sheet1.Range("B1", Sheet1.Cells(Rows.Count, "B").End(xlUp))
.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],Sheet2!C[-1],1,FALSE)),""NO"",""YES"")"
.Value = .Value
End With
End Sub
NOTES
笔记
Assumes lookup values are in sheet1 column A, the lookup column is sheet2 column A. Places Yes/No in sheet1 column B to show if value found or not. Vlookup is not case sensitive. You will need to change the R1C1 references/columns to suit your workbook.
假设查找值在工作表 1 列 A 中，查找列是工作表 2 列 A。将是/否放在工作表 1 列 B 中以显示是否找到值。Vlookup 不区分大小写。您将需要更改 R1C1 引用/列以适合您的工作簿。
It will execute very fast.
它会执行得非常快。
