Runtime error using Cell.Find command

[Guest]

Hello,
I have designed a spreadsheet with 24 worksheets. I am trying to design a
macro that will search for a specific date that is embedded inside one of my
worksheets within the workbook. When I run the script below, the Cells.Find
command stalls the macro. Specifically I get the following error ---> Runtime
error ‘91’: Object variable or with block not set. How do you suggest I fix
this?


Sub test()
Worksheets("LeaveRecord").Activate
ActiveSheet.Next.Select
Cells.Find(What:="11/11/2007").Activate

End Sub

Thanks in advance for your help,
Ellen

 

[Norman Jones]

Hi Ellen,

Try replacing

Cells.Find(What:="11/11/2007").Activate

with

On Error Resume Next
ActiveSheet.Cells.Find(What:="11/11/2007").Activate
On Error GoTo 0

 

[Dave Peterson]

Working with dates in code can be frustrating.

I'd try:

Dim FoundCell as range
with worksheets("yourworksheetnamehere")
.activate
Set FoundCell = .Cells.Find(what:=dateserial(2007,11,11), _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
msgbox "not found"
else
foundcell.select
end if

 

[Guest]

Hello Dave,
Thanks for your help. I pasted the script and only substituted your
worksheet name with the one I am using. I ran it and got the following error.


Runtime error ‘1004’
Activate method of Worksheet class failed



Sub Last_Post()
Dim FoundCell As Range
With Worksheets("2007-pp24")
..Activate
Set FoundCell = .Cells.Find(what:=DateSerial(2007, 11, 11), _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.Select
End If
End Sub

Any suggestions?

Thanks,
Ellen

 

[Dave Peterson]

I'm gonna guess that you misspelled "2007-pp24"

Maybe there's an extra space????

And this won't make a difference for the error message, but I should have used

..select
instead of
..activate

(But again, that's not the cause of the 1004 error)

 

[Guest]

Thanks, so much, Dave. Your script was very, very helpful. We've got it
working now.

Ellen