在“查找”vba 中找不到项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11972850/
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
item not found in "Find" vba
提问by orangehairbandit
I'm looking for user ID #s from a list. However some users no longer exist. I've tried the testmethod, the on error go tomethod, and if err.number<> 0 thenmethod. I still receive the Run-time error '91': object variable or with block variable not set. The number does not exist on the the list. Below is my code with a couple of fruitless attempts
我正在从列表中查找用户 ID #s。但是有些用户已经不存在了。我已经尝试了test方法，on error go to方法和if err.number<> 0 then方法。我仍然收到Run-time error '91': object variable or with block variable not set. 该号码不存在于列表中。下面是我的代码，经过几次徒劳的尝试
On Error GoTo errorLn
If Err.Number <> 0 Then
GoTo errorLn
End If
Cells.Find(What:=uSSO, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
What other options are there? Or am I misplacing the lines of "error"? I have tried it before and after the "cells.Find..."
还有哪些其他选择？还是我放错了“错误”行？我在“cells.Find...”之前和之后都试过了
采纳答案by RocketDonkey
I believe you'll need to restructure it just a little bit. It is not the bestpractice to handle errors with On Error Resume Next, but you could try this:
我相信你需要稍微调整一下。使用 处理错误不是最佳做法On Error Resume Next，但您可以尝试以下操作：
On Error Resume Next
Cells.Find(What:=uSSO, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
If Err.Number <> 0 Then
'''Do your error stuff'''
GoTo errorLn
Else
Err.Clear
End If
Does that work for your situation?
这对你的情况有用吗？
来源：http: //www.mrexcel.com/forum/excel-questions/143988-check-if-value-exists-visual-basic-applications-array.html
回答by enderland
You will want to do something different than have message boxes, presumably.
您可能想做一些与消息框不同的事情。
Dim myCell As Range
Set myCell = Cells.Find(What:=uSSO, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If (Not myCell Is Nothing) Then
MsgBox "something!"
Else
MsgBox "nothing"
End If
回答by Siddharth Rout
Try this
尝试这个
Sub Sample1()
Dim oSht As Worksheet
Dim uSSO As String
Dim aCell As Range
On Error GoTo Whoa
'~~> Change this to the relevant sheet
Set oSht = Sheets("Sheet1")
'~~> Set User ID here
uSSO = "User ID"
Set aCell = oSht.Cells.Find(What:=uSSO, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'~~> Check if found or not
If Not aCell Is Nothing Then
MsgBox "Value Found in Cell " & aCell.Address
Else
MsgBox "Value Not found"
End If
Exit Sub
Whoa:
MsgBox Err.Description
End Sub
I also would recommend reading this link where I have covered .Findand .FindNext
我还建议阅读此链接，我已经涵盖.Find和.FindNext
Topic: .Find and .FindNext In Excel VBA
主题：Excel VBA 中的 .Find 和 .FindNext
Link: http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/
链接：http: //www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/
回答by Amit Kohli
Just for posterity, this is how you do it w/out error handling (from: http://www.mrexcel.com/forum/excel-questions/519070-visual-basic-applications-error-handling-when-dealing-cells-find.html).
只是为了后代，这就是没有错误处理的方法（来自：http: //www.mrexcel.com/forum/excel-questions/519070-visual-basic-applications-error-handling-when-dealing-单元格-find.html）。
Dim rngFound As Range
Set rngFound = Sheets("WhateverSheet").UsedRange.Find(What:="SoughtValue",LookIn:=xlFormulas)
If Not rngFound Is Nothing Then
'you found the value - do whatever
Else
' you didn't find the value
End if
