If .find method finds nothing?

[GBagley]

I have a macro that uses the find method to search a column for a value. It's within an If statement, but if the value isn't found in the column then I get an 'Object variable not set' error. I would like for it to see the If statement as false in this case. What's the best way to do this without using an On Error statement?

If Sheets(1).Range("A:A").Find(cell).offset(0, 21) = "Y" Then
Variable1 = True
Else
Variable1 = False

 

[VoG]

Perhaps like this

Dim Found As Range
Set Found = Sheets(1).Range("A:A").Find(what:=cell)
If Found Then
    If Found.Offset(0, 21) = "Y" Then
        Variable1 = True
    Else
        Variable1 = False
    End If
Else
    MsgBox "Not found"
    Exit Sub
End If

 

[VoG]

In fact that could be simplified

Dim Found As Range
Set Found = Sheets(1).Range("A:A").Find(what:=cell)
If Found Then
    variable1 = Found.Offset(0, 21) = "Y"
Else
    MsgBox "Not found"
    Exit Sub
End If

 

[GBagley]

Perhaps like this

Dim Found As Range
Set Found = Sheets(1).Range("A:A").Find(what:=cell)
If Found Then
    If Found.Offset(0, 21) = "Y" Then
        Variable1 = True
    Else
        Variable1 = False
    End If
Else
    MsgBox "Not found"
    Exit Sub
End If

Hmm...closer, but I'm getting a type mismatch on "If Found Then". Found is returning a string (the contents of the cell). If I do Find(what:=cell).Address, then I get an object variable not set error.

 

[GBagley]

Dim Found As Range
Set Found = Sheets(1).Range("A:A").Find(what:=cell)
If Found Is Nothing Then
    Else
    variable1 = Found.Offset(0, 21) = "Y"
End If

Ah, I think I figured it out. 'If Found Is Nothing Then' worked.


While we're here, is there a syntax for the reverse, 'If Found Is Not Nothing'? I tried 'If Found Not Nothing' but the VBE autocorrects it to 'If Not Found', is that the same or is it doing something different than I imagine?