On error after "find me"

[Claire Jackson]

Hi all, I'm trying to search for a word in excel but if the word isn't found I'm getting a debug so I've tried the following but it isn't working. What am I doing wrong?

Sub FindMe()

On Error GoTo ErrorHandler

Cells.Find(What:=InputBox("Please enter your search criteria", "Search"), _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

ErrorHandler:
MsgBox "Job name not found"

End Sub

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Sub FindMe()
    Dim FoundCell   As Range
    Dim Search      As Variant
    
    Do
        Search = InputBox("Please enter your search criteria", "Search")
        'cancel pressed
        If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
    
    Set FoundCell = Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    
    If Not FoundCell Is Nothing Then
    
        With FoundCell
        
        'search value found - do something
        
        End With
        
    Else
        
        MsgBox Search & Chr(10) & "Job name Not found", 48, "Not Found"
        
    End If
    
End Sub

Dave

 

[bebo021999]

If find = nothing: then return nothing, not error.
Try this instead:

Sub FindMe()
Dim f
set f = Cells.Find(What:=InputBox("Please enter your search criteria", "Search"), _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
f.select
If f is nothing then MsgBox "Job name not found"
End Sub

 

[Claire Jackson]

PERFECT!!! Thank you so much

 

[Claire Jackson]

PERFECT!!! Thank you so much

Hi,
try this update to your code & see if does what you want

Sub FindMe()
    Dim FoundCell   As Range
    Dim Search      As Variant
   
    Do
        Search = InputBox("Please enter your search criteria", "Search")
        'cancel pressed
        If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
   
    Set FoundCell = Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
   
    If Not FoundCell Is Nothing Then
   
        With FoundCell
       
        'search value found - do something
       
        End With
       
    Else
       
        MsgBox Search & Chr(10) & "Job name Not found", 48, "Not Found"
       
    End If
   
End Sub

Dave

Actually, it's not quite right, it doesn't navigate to the word in the spreadsheet any more

 

[Alex Blakenburg]

@bebo021999 - your code suffers from the same issue as the original code.
It is testing for nothing "after" it tries to select the cell, which errors out if it found nothing.

f.select
If f is nothing then MsgBox "Job name not found"

 

[dmt32]

Actually, it's not quite right, it doesn't navigate to the word in the spreadsheet any more

I left that bit open for whatever you wanted to do with the code - just add line in bold

Sub FindMe()
    Dim FoundCell   As Range
    Dim Search      As Variant
    
    Do
        Search = InputBox("Please enter your search criteria", "Search")
        'cancel pressed
        If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
    
    Set FoundCell = Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    
    If Not FoundCell Is Nothing Then
    
    
        
        'search value found - do something
        FoundCell.Select
        
        
    Else
        
        MsgBox Search & Chr(10) & "Job name Not found", 48, "Not Found"
        
    End If
    
End Sub

Dave

 

[Claire Jackson]

I left that bit open for whatever you wanted to do with the code - just add line in bold

Sub FindMe()
    Dim FoundCell   As Range
    Dim Search      As Variant
   
    Do
        Search = InputBox("Please enter your search criteria", "Search")
        'cancel pressed
        If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
   
    Set FoundCell = Cells.Find(What:=Search, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
   
    If Not FoundCell Is Nothing Then
   
   
       
        'search value found - do something
        FoundCell.Select
       
       
    Else
       
        MsgBox Search & Chr(10) & "Job name Not found", 48, "Not Found"
       
    End If
   
End Sub

Dave

Whoop, thank you x

 

[dmt32]

Whoop, thank you x

Most welcome, glad we were able to help & appreciate feedback

Dave