This works perfectly if the date is keyed as an actual date. However, my dates are calculated based on the first date in the row (i.e., =NK5+7).

If I display the date using MsgBox, it looks like a date. If I compare Range("nu5") = CLng(Date) it returns True.
But when I run the macro, it displays "Nothing found."

I'm confused. Thanks for your help... MPV

On Monday, January 14, 2008 2:06 PM Pat Hawkins wrote:

I need an Excel Macro that will select the current date from a column of
dates. This macro needs to run automatically when the worksheet is opened.
If anybody has any tips on how to do this, I would greatly appreciate it!

Thanks in advance
Pat


On Monday, January 14, 2008 2:19 PM Ron de Bruin wrote:

Try this macro

If you have date's in column A then this example will select the cell with today's date.

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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Pat Hawkins" wrote in message ...


On Monday, January 14, 2008 3:50 PM Pat Hawkins wrote:

Ron

Thanks for your quick response. One more question. In the spreadsheet I am
working on there are headings for each month. Each month is entered as
'mm/01/yyyy', but formatted to display as 'mmmm-yy'. As an example, the
January heading is entered as 01/01/2008 but is formatted as a custom date
to display as 'January-08'. How can I modify this macro, to go to the
heading for the current month?

Thanks again.

Pat

"Ron de Bruin" wrote in message
...


On Monday, January 14, 2008 4:02 PM Ron de Bruin wrote:

Hi Pat

With the headers in row 1 of "Sheet1" try this one

Sub Find_Date_test()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(DateSerial(Year(Date), Month(Date), 1))
With Sheets("Sheet1").Rows("1:1")
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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Pat Hawkins" wrote in message ...


On Monday, January 14, 2008 6:08 PM Pat Hawkins wrote:

Thanks for all your help. That worked!

Pat


On Tuesday, April 27, 2010 7:40 PM James Uzy wrote:

Hi and thanks for the post as this works witn a single sheet. I am trying to get excel to go to the date on multiple sheets when the workbook opens.

The code below allows this to happen but on one sheet only. Can someone please advise how to change this to include multiple sheets (there are only 4).

Very new to VBA and these forums have been gold. thanks


Submitted via EggHeadCafe
C# In Depth Second Edition - An Interview with Jon Skeet
http://www.eggheadcafe.com/tutorials...jon-skeet.aspx