selection.Find sometimes works sometimes not

rholdren

Board Regular
Joined
Aug 25, 2016
Messages
82
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,507
Office Version
365
Platform
Windows
If you want it to search the whole sheet, change:
Code:
[COLOR=#333333]Selection.Find(...
to
[/COLOR]
Code:
[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

Board Regular
Joined
Aug 25, 2016
Messages
82
I was using
If Find Is Nothing Then Exit Sub
for the error handling but it gives me an error now as well.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,507
Office Version
365
Platform
Windows
Here is one way:
Code:
    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
 
Last edited:

Forum statistics

Threads
1,082,548
Messages
5,366,227
Members
400,880
Latest member
dwb

Some videos you may like

This Week's Hot Topics

Top