vba 在excel vba中查找`find`方法是否返回`nothing`
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29066633/
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
find if `find` method returns `nothing` in excel vba
提问by dwstein
I'm trying to find an id in a list and get it's address, but also deal with a situation if nothing is found.
我试图在列表中找到一个 id 并获取它的地址，但如果没有找到任何东西，我也会处理这种情况。
Here's what I have:
这是我所拥有的：
Function find_in_two_ranges_two_sheets(ws1 As String, col1 As Integer) As Range
Dim rows1 As Integer
rows1 = Get_Rows_Generic(ws1, 1)
Dim range1 As Range ' range of first search
With Worksheets(ws1)
Set range1 = .Range(.Cells(1, col1), .Cells(rows1, col1))
End With
Dim found1 As Range
Set found1 = range1.Find("test id", LookIn:=xlValues)
If found1 = Nothing Then
MsgBox "nothing"
Else
MsgBox found1.AddressLocal
End If
Set find_in_two_ranges_two_sheets = range1
End Function
Sub test_stuff()
Dim x As Range
Set x = find_in_two_ranges_two_sheets("usersFullOutput.csv", 1)
MsgBox x.Address
End Sub
When I run test_stuff()I get an error in the function in the line If found1 = Nothing Thenwith the word Nothinghighlighted. "Compile error; Invalid Use of Object". Not sure what to do.
当我运行时，test_stuff()我在突出显示If found1 = Nothing Then单词的行中的函数中出现错误Nothing。“编译错误；对象的无效使用”。不知道该怎么办。
回答by tospig
To check the rangeobject you need to use isinstead of =:
要检查range您需要使用的对象is而不是=：
If found1 Is Nothing Then
MsgBox "nothing"
Else
MsgBox found1.AddressLocal
End If
Explanation:
解释：
Nothingis the uninitialized state of an object variable. An object cannot be a simple variable such as a number or a string, so it can never be 0 or "". It must be a more comprehensive structure (a text box, form, recordset, querydef, ...)
Nothing是对象变量的未初始化状态。对象不能是简单的变量，例如数字或字符串，因此它永远不能是 0 或“”。它必须是一个更全面的结构（文本框、表单、记录集、querydef、...）
Since it is not a simple value, you cannot test if it is equal to something. VBA has an Iskeyword that you use.
由于它不是一个简单的值，因此您无法测试它是否等于某物。VBA 有一个Is您使用的关键字。
回答by rgo
Does that solve it? If so, check the solved checkbox.
那能解决吗？如果是这样，请选中已解决的复选框。
Another way to locate matching key values and the address is to use the Match function which returns the row of the matching key value if found. It returns #N/A if not found. You can then loop through the column using if(ISNA(Rangeaddress))...
定位匹配键值和地址的另一种方法是使用 Match 函数，如果找到则返回匹配键值所在的行。如果未找到，则返回 #N/A。然后，您可以使用 if(ISNA(Rangeaddress))... 遍历该列。
