Runtime error using Cell.Find command
 

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

Runtime error using Cell.Find command
 

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


---
Regards,
Norman


"EllenM" wrote in message
...
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

Runtime error using Cell.Find command
 

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


EllenM wrote:

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



--

Dave Peterson

Runtime error using Cell.Find command
 

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" wrote:

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


EllenM wrote:

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



--

Dave Peterson

Runtime error using Cell.Find command
 

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)

EllenM wrote:

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" wrote:

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


EllenM wrote:

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



--

Dave Peterson


--

Dave Peterson

Runtime error using Cell.Find command
 

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

Ellen

"Dave Peterson" wrote:

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)

EllenM wrote:

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" wrote:

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


EllenM wrote:

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



--

Dave Peterson


--

Dave Peterson