Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Error 9 Subprocedure Out of Range
Sub Find_Todays_Date()
Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Sheet1").Range("A:A") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Nothing found" End If End With End Sub This is my code. It is supposed to goto the date when executed. It works fine on my machine, but I tried putting it on someone else's and it gave me a data error message. It said something like the subprocedure is out of range. It was Data Error 9. I've checked the refrence style setting in the options pannel and they are the same on both machines. We are both using MS 2003, but come to think of it Im not sure which SP the other one is using... Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Error 9 Subprocedure Out of Range
An error 9 (Subscript Out Of Range) error occurs when you attempt to
access by name an element of an array or collection when no such named element exists. For example, in your code you have With Sheets("Sheet1").Range("A:A") If there is no worksheet named "Sheet1", you'll get an error 9 when you attempt to access "Sheet1". My guess is that your user has no sheet named "Sheet1". If you always want to use the first worksheet in the workbook, you can use With Worksheets(1).Range("A:A") If the sheet isn't predictable by its position, you'll need to require the user to be on the appropriate sheet when running the code, in which case you can use With ActiveSheet.Range("A:A") Otherwise, you'll need to prompt the user for the appropriate sheet. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 5 Dec 2008 14:18:52 -0800, Steve wrote: Sub Find_Todays_Date() Dim FindString As Date Dim Rng As Range FindString = CLng(Date) With Sheets("Sheet1").Range("A:A") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True Else MsgBox "Nothing found" End If End With End Sub This is my code. It is supposed to goto the date when executed. It works fine on my machine, but I tried putting it on someone else's and it gave me a data error message. It said something like the subprocedure is out of range. It was Data Error 9. I've checked the refrence style setting in the options pannel and they are the same on both machines. We are both using MS 2003, but come to think of it Im not sure which SP the other one is using... Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro subprocedure | Excel Discussion (Misc queries) | |||
Hyperlink to a subprocedure in an other workbook | Excel Worksheet Functions | |||
VBA code: call subprocedure | Excel Programming | |||
how to call subprocedure from within procedure? | Excel Programming | |||
Passing range to subprocedure - maybe? | Excel Programming |