vba 检查其他两张工作表中是否存在单元格值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11509351/
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
checking if cell value exists in two other sheets
提问by Jared
I am trying to track IDs within several different sheets and I want to be able to give a True value for one criteria and false for another. Here is my pseudo code for what I am trying to accomplish.
我正在尝试在几个不同的工作表中跟踪 ID，我希望能够为一个标准提供 True 值，为另一个标准提供 false 值。这是我要完成的伪代码。
IF sheet1.A1.value EXISTS AND DOES NOT EXIST IN sheet2.A:A OR sheet3.A:A
THEN RETURN "true"
ELSE RETURN "false"
回答by Siddharth Rout
Try this
尝试这个
Sub Sample()
Dim SearchString As String
SearchString = "Blah"
If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Columns(1), SearchString) > 0 And _
Application.WorksheetFunction.CountIf(Sheets("Sheet2").Columns(1), SearchString) = 0 And _
Application.WorksheetFunction.CountIf(Sheets("Sheet3").Columns(1), SearchString) = 0 Then
'~~> Display relevant message
Else
'~~> Display relevant message
End If
End Sub
回答by danielpiestrak
You could also do this with just an excel formula like this:
你也可以只用一个像这样的excel公式来做到这一点：
=IF(AND(len('Sheet1'!A1)>0 , ISERROR(MATCH('Sheet1'!A1,'Sheet2'!A:A,0)), ISERROR(MATCH('Sheet1'!A1,'Sheet3'!A:A,0))) , "True", "False")
This Reads:
这读到：
- If the following three condiitons are met, then True, otherwise False
- Condition 1: Sheet1's Cell A1 exists
- Check if the length of any value in it is greater than 0
- Condition 2: Sheet1's Cell A1 not in Sheet 2's column A
- Run a match formula to check this
- If the match returns an error, then it was not found
- Condition 3
- Run a match formula to check this
- If the match returns an error, then it was not found
- 如果满足以下三个条件，则为真，否则为假
- 条件 1：Sheet1 的单元格 A1 存在
- 检查其中任何值的长度是否大于 0
- 条件 2：工作表 1 的单元格 A1 不在工作表 2 的 A 列中
- 运行匹配公式来检查这个
- 如果匹配返回错误，则未找到
- 条件 3
- 运行匹配公式来检查这个
- 如果匹配返回错误，则未找到
