Data Error 9 Subprocedure Out of Range

[Steve]

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?

 

[Chip Pearson]

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)