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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>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
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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

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" <(E-Mail Removed)> wrote in message news:u9%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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
>
>

Thanks for all your help. That worked!

Pat

"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:u9%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>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
>>

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




Ron de Bruin wrote:

Try this macroIf you have date's in column A then this example will select the
14-Jan-08

Try this macr

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 Dat
Dim Rng As Rang
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 The
Application.Goto Rng, Tru
Els
MsgBox "Nothing found
End I
End Wit
End Su


--

Regards Ron de Brui
http://www.rondebruin.nl/tips.ht

"Pat Hawkins" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...

Previous Posts In This Thread:

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

Auto start Macro to go to today's date in worksheet
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 advanc
Pat

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

Try this macroIf you have date's in column A then this example will select the
Try this macr

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 Dat
Dim Rng As Rang
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 The
Application.Goto Rng, Tru
Els
MsgBox "Nothing found
End I
End Wit
End Su


--

Regards Ron de Brui
http://www.rondebruin.nl/tips.ht

"Pat Hawkins" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...

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

RonThanks for your quick response. One more question.
Ro

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

Pa

"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

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

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

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

Sub Find_Date_test(
Dim FindString As Dat
Dim Rng As Rang
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" <(E-Mail Removed)> wrote in message news:u9%(E-Mail Removed)...

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

Thanks for all your help. That worked!
Thanks for all your help. That worked!

Pat


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Reflection Effect
http://www.eggheadcafe.com/tutorials...on-effect.aspx

Sub Find_Todays_Date()
Dim FindString As Date
Dim Rng As Range
Dim ws As Worksheet
FindString = CLng(Date)
For Each ws In ActiveWorkbook.Worksheets
With ws.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 on " & ws.Name
End If

End With
Next
End Sub


Gord Dibben MS Excel MVP


On Tue, 27 Apr 2010 16:40:28 -0700, 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
>
>
>
>
>Ron de Bruin wrote:
>
>Try this macroIf you have date's in column A then this example will select the
>14-Jan-08
>
>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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>
>Previous Posts In This Thread:
>
>On Monday, January 14, 2008 2:06 PM
>Pat Hawkins wrote:
>
>Auto start Macro to go to today's date in worksheet
>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 macroIf you have date's in column A then this example will select the
>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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>
>On Monday, January 14, 2008 3:50 PM
>Pat Hawkins wrote:
>
>RonThanks for your quick response. One more question.
>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" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>
>On Monday, January 14, 2008 4:02 PM
>Ron de Bruin wrote:
>
>Hi PatWith the headers in row 1 of "Sheet1" try this oneSub Find_Date_test()
>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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...


>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...


>>>> 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" <(E-Mail Removed)> wrote in message news:u9%(E-Mail Removed)...


>>>>> 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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...


>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...


>>>> 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" <(E-Mail Removed)> wrote in message news:u9%(E-Mail Removed)...


>>>>> 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

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 <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>
>
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>
>
>>>>> 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" <(E-Mail Removed)> wrote in message news:u9%(E-Mail Removed)...
>
>
>>>>>> 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