item not found in "Find" vba

Question

I'm looking for user ID #s from a list. However some users no longer exist. I've tried the test method, the on error go to method, and if err.number<> 0 then method. 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

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..."

Answer 1

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

Answer 2

I believe you'll need to restructure it just a little bit. It is not the best practice to handle errors with On Error Resume Next, but you could try this:

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?

Source: http://www.mrexcel.com/forum/excel-questions/143988-check-if-value-exists-visual-basic-applications-array.html

Answer 3

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 .Find and .FindNext

Topic: .Find and .FindNext In Excel VBA

Link: https://web.archive.org/web/20160316214709/https://siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/