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
<(E-Mail Removed)> 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?
Similar Threads
