Runtime error using Cell.Find command

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Hi Ellen,

Try replacing
Cells.Find(What:="11/11/2007").Activate
Click to expand...


with

On Error Resume Next
ActiveSheet.Cells.Find(What:="11/11/2007").Activate
On Error GoTo 0
 
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
 
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
 
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)
 
Thanks, so much, Dave. Your script was very, very helpful. We've got it
working now.

Ellen
 
Back
Top