selection.Find sometimes works sometimes not

[rholdren]

Hi,

I've got this section of code that runs perfectly well in another sub but when I move it to a new sub I get a runtime error 91. Any help would be really appreciated. From Selection.find to .Activate highlights yellow

Sheets("ADP_Times_All_Teams").Select
Selection.Find(What:="AguirreValinda", After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -4).Select
Selection.Copy
Sheets("Juliya Hours").Select
Range("C4").Select
ActiveSheet.Paste

Thanks

 

[Joe4]

If you want it to search the whole sheet, change:

[COLOR=#333333]Selection.Find(...

to
[/COLOR]

[COLOR=#333333]Cells.Find(...[/COLOR]

Also, error 91 is the error message it will return if it cannot find what you are looking for. You probably need to account for that possibility with some error handling.

 

[rholdren]

I was using
If Find Is Nothing Then Exit Sub
for the error handling but it gives me an error now as well.

 

[Joe4]

Here is one way:

    Dim cell As Range

    Sheets("ADP_Times_All_Teams").Select
    On Error Resume Next
    Set cell = Cells.Find(What:="AguirreValinda", After:=ActiveCell, LookIn:= _
        xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
    On Error GoTo 0
    
    If Not cell Is Nothing Then
        Sheets("Juliya Hours").Range("C4") = cell.Offset(0, -4)
    End If