How do I display an error message if Find Macro is unsuccessful

**dcgrove** · 10-29-2008, 06:40 PM

The code below searches the entire sheet for an email address that the use has inputted into cell E3. How can I make this code show an error message if there are no matches found?

Sub delete2()
'
' delete2 Macro
' Macro recorded 10/29/2008 by Clayton Grove
'

'
    Range("E3").Select
    Cells.find(What:=Range("E3"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
     
    Selection.ClearContents
      Range("E3").Select
    Selection.ClearContents

End Sub

Thanks!
Clayton Grove

**Tirren** · 10-29-2008, 06:44 PM

Sub delete2()
'
' delete2 Macro
' Macro recorded 10/29/2008 by Clayton Grove

    'set up error checker
    On Error Goto Delete2Error 
    Range("E3").Select
    Cells.find(What:=Range("E3"), After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
     
    Selection.ClearContents
      Range("E3").Select
    Selection.ClearContents

    'stops error checker and returns to normal
    On Error goto 0     
    Exit Sub       'exits sub or Delete2Error will run anyway
'if error found, comes here
Delete2Error: 
    msgbox "There was an error"
End Sub

Use above and modify to your hearts content.

Bob's is better as it does more of what you want.

**Bob Phillips** · 10-29-2008, 06:46 PM

Sub delete2()
'
' delete2 Macro
' Macro recorded 10/29/2008 by Clayton Grove
'

'
    Set cell = Cells.Find(What:=Range("E3").Value, _
                        After:=Range("E3"), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
    If cell Is Nothing Then
    
        MsgBox "Nothing found"
    Else
    
        cell.ClearContents
        Range("E3").ClearContents
    End If
    
End Sub

**dcgrove** · 10-29-2008, 06:52 PM

Hmm, its not working. I could have done something wrong, but when I put random text in cell e3 and run the macro, I do not get an error.

**shg** · 10-29-2008, 06:56 PM

Maybe ...

Sub delete2()
    Dim cell    As Range

    Set cell = Cells.Find(What:=Range("E3").Value, _
                          After:=Range("E3"), _
                          LookIn:=xlFormulas, _
                          LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
                          SearchFormat:=False)
    If cell.Address(0, 0) <> "E3" Then
        MsgBox "Nothing (else) found"
    Else
        cell.ClearContents
        Range("E3").ClearContents
    End If
End Sub

**dcgrove** · 10-29-2008, 07:00 PM

Apparently I am rather dense as I have tried all three and none of them display an error message if there is any text in cell E3 if it is blank, they all work dandy. I have even removed all of the address from the sheet to test it out so I know it is not finding something. I am at a loss now.