Hi PatWith the headers in row 1 of "Sheet1" try this oneSubFind_Date_test()
 

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


On Wednesday, November 24, 2010 2:22 PM M V wrote:

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


Submitted via EggHeadCafe
SharePoint 2010 Visual Web Parts using Visual Studio 2010, Feature Designer and Package Designer
http://www.eggheadcafe.com/tutorials...-designer.aspx

Hi PatWith the headers in row 1 of "Sheet1" try this oneSub Find_Date_test()
 

Dates are 7 days apart.................(i.e., =NK5+7)

I would think today's date would be found only every 7th day?

Also............are your dates actually on "Sheet1"


Gord Dibben MS Excel MVP



On Wed, 24 Nov 2010 19:23:13 GMT, M V wrote:

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


On Wednesday, November 24, 2010 2:22 PM M V wrote:

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


Submitted via EggHeadCafe
SharePoint 2010 Visual Web Parts using Visual Studio 2010, Feature Designer and Package Designer
http://www.eggheadcafe.com/tutorials...-designer.aspx

Hi PatWith the headers in row 1 of "Sheet1" try this oneSubFind_Date_test()
 

I too would like to know if it is possible to search for the "displayed date" and not a date in a cell, finding a date in a cell only helps me find the first date I have entered, I am using a formula to auto fill the following date in cells not next to each other.

Could the above VBA code work with some changes?

Submitted via EggHeadCafe
Microsoft ASP.NET For Beginners
http://www.eggheadcafe.com/training-...NET/7/ASP.aspx

Hi PatWith the headers in row 1 of "Sheet1" try this oneSub Find_Date_test()
 

And which above VBA code would that be?


Gord Dibben MS Excel MVP

On Mon, 06 Dec 2010 15:28:16 -0600, William Moser wrote:

I too would like to know if it is possible to search for the "displayed date" and not a date in a cell, finding a date in a cell only helps me find the first date I have entered, I am using a formula to auto fill the following date in cells not next to each other.

Could the above VBA code work with some changes?

Submitted via EggHeadCafe
Microsoft ASP.NET For Beginners
http://www.eggheadcafe.com/training-...NET/7/ASP.aspx