How would I go about making a script to automatically find and select today's
date when I activate the worksheet (not the workbook)?

I have dates in row A and I want to search for today's date and
automatically go that cell when I click on the worksheet's tab. The
worksheet is called "Order Fulfillment".

Jason

Macro written by Ron deBruin.......with slight modifications.

Copy/paste to a General Module.

Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.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

Place the following in the sheet module.

Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub

BTW..........A is not a row. A is a column.


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 18:39:01 -0700, Jason
wrote:

How would I go about making a script to automatically find and select today's
date when I activate the worksheet (not the workbook)?

I have dates in row A and I want to search for today's date and
automatically go that cell when I click on the worksheet's tab. The
worksheet is called "Order Fulfillment".

Actually, it was Row 1. Muchos Gracias!

"Gord Dibben" wrote:

Jason

Macro written by Ron deBruin.......with slight modifications.

Copy/paste to a General Module.

Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.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

Place the following in the sheet module.

Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub

BTW..........A is not a row. A is a column.


Gord Dibben MS Excel MVP

On Thu, 17 Aug 2006 18:39:01 -0700, Jason
wrote:

How would I go about making a script to automatically find and select today's
date when I activate the worksheet (not the workbook)?

I have dates in row A and I want to search for today's date and
automatically go that cell when I click on the worksheet's tab. The
worksheet is called "Order Fulfillment".

Hi Jason,

If you want to find in the Row 1, you can use the syntax below to get the
Row 1.
Dim o As Range
Set o = Sheet1.Rows(1)

And then you can use Macro provided by Gord.

If you have any concern, please feel free to let me know.

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

I still can't get it to work properly. It keeps saying the date can't be
found, even though it is there.

Can you post the exact code I need?

My data looks like this:

A B C D E
1 16-Aug 17-Aug 18-Aug 19-Aug 20-Aug
2 data data data data data



""Peter Huang" [MSFT]" wrote:

Hi Jason,

If you want to find in the Row 1, you can use the syntax below to get the
Row 1.
Dim o As Range
Set o = Sheet1.Rows(1)

And then you can use Macro provided by Gord.

If you have any concern, please feel free to let me know.

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Jason,

Here is the exact code I use at my side.

Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = "2006-8-19"
With ActiveSheet.Rows(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

1. I build a Worksheet as your suggestion
A B C D E
16-Aug 17-Aug 18-Aug 19-Aug 20-Aug

2. Select any one cell but not the cell 19-Aug

3. Run the macro above, the cell " 19-Aug" will be selected.


Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Just to add to Peter's response...

Sometimes changing the date to a long will help:

Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = dateserial(2006,8,19) '"2006-8-19"
With ActiveSheet.Rows(1)
Set rng = .Find(What:=clng(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



"Peter Huang [MSFT]" wrote:

Hi Jason,

Here is the exact code I use at my side.

Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = "2006-8-19"
With ActiveSheet.Rows(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

1. I build a Worksheet as your suggestion
A B C D E
16-Aug 17-Aug 18-Aug 19-Aug 20-Aug

2. Select any one cell but not the cell 19-Aug

3. Run the macro above, the cell " 19-Aug" will be selected.

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--

Dave Peterson