Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I handle error conditions with the FIND command? | Excel Worksheet Functions | |||
Listing of cell references from a FIND All command. | Excel Discussion (Misc queries) | |||
Runtime 91 error due to cells.find statement !??! | Excel Programming | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Runtime error 9 in "How to find a date in a range with VBA ?" | Excel Programming |