Excel and Visual Basic Dates

[Imagineer]

I have an Excel spreadsheet in which I need to go to the column corresponding to the current date. I created a command button with the following sub:

Private Sub bActualToday_Click()
' Variables
Dim dToday As Date
Dim rDates, rToday As Range

rDates = Worksheets("Actual&quot.Range("J2:IS2&quot

dToday = Date
rToday = rDates.Find(dToday, LookIn:=xlValues)
rToday.Activate

End Sub

I'm thinking that the problem is the type mismatch between dToday and what Find is expecting or the way Excel stores dates in cells. I thought I read once that Excel stores dates as doubles but redefining dToday as a double doesn't work.

TIA,
Imagineer
[sig][/sig]

 

[BobCrawf]

I think you want the address of the cell containing today.

try something like:

Private Sub bActualToday_Click()

With ActiveSheet.Range("a2:is2&quot
Range(.Find(Date, LookIn:=xlValues).Address).Activate
End With

End Sub

This will get you to the first occurance of the todays date in the range A2:IS2 [sig][/sig]

 

[Imagineer]

I discovered the Show method. Along with your suggestion I thought this would work but I keep getting following error:

Run-time Error '1004'
Unable to get the Find property of the Range Class.

I've converted the statement to
Range(ActiveSheet.Range("J2:IS2&quot.find(Date, _ LookIn:=xlValues).Address).Show

Still the same error. [sig][/sig]

 

[BobCrawf]

Private Sub bActualToday_Click()
On Error GoTo ErrorHandler
With ActiveSheet.Range("a2:IS2&quot
Range(.Find(Date, LookIn:=xlValues).Address).Activate
End With
ErrorHandler: ' Error-handling routine

End Sub
Me thinks you are attempting to be too cute with this code.
You need the with and end with.

You also need an error handler because if the date is not in your range you will get an error. Also you need to be aware that TODAY() looks line 10/01/2000 1:23:55 PM
where Date() is 10/01/2000

[sig][/sig]