set up macro to goto cell with "today" in

[Stuart WJG]

I have mutiple worksheets with daily information covering 6 months. I have
set up formula that highlights column with "today" realting to current date.
I would like to set up macro on each worksheet that I can run to take me to
"todays" date

 

[Bernie Deitrick]

Stuart,

For Each Wks In Activeworkbook.Worksheets
Wks.Select
Wks.Range("B:B").Find(Format(Now(), "mm/dd/yy"), , xlValues).Select
Next Wks

Change the mm/dd/yy to reflect the formatting that you use for your dates.

HTH,
Bernie
MS Excel MVP

 

[Stuart WJG]

Bernie
Not sure what you mean by Wks
This is is what I have

A1 A2 A3 TODAY A5 A6
10-Mar-08 11-Mar-08 12-Mar-08 13-Mar-08 14-Mar-08 15-Mar-08

I would like macro to goto A4 in this case or A5 if tomorrow

Thanks for you help
Stuart

 

[Fred Smith]

That's exactly what Bernie's macro does. Did you try it?

Regards,
Fred.

 

[Bernie Deitrick]

Stuart,

Try this:

Sub GoToToday()
Range("A:A").Find(Format(Now(), "dd-mmm-yy"), , xlValues).Select
End Sub

This will find today's date on the currently active worksheet.

HTH,
Bernie
MS Excel MVP

 

[Lori]

Another way is to insert a Name (Edit Menu) e.g. "T" which refers to:
=INDEX(!$A:$A,MATCH(TODAY(),!$A:$A,0))
Then just type "t" into the Name Box (to the left of the formula bar)

....OR you can choose Record New Macro (tools Menu) and type in the Name Box:
index(a:a,match(today(),a:a,0))
stop recording and assign this to a button

 

[Stuart WJG]

Hi
This is the macro I have done
Sub GoToToday()
Range("F2:HP2").Find(Format(Now(), "dd-mm-yy"), , xlValues).Select
End Sub
When I run i get this error
Run Time error: "91"
Object variable or With block variable not set
I am running Excel 2007

 

[Bernie Deitrick]

Stuart,

Your origianl post shows a format of dd-mmm-yy

To be certain, try this:

Sub TryNow()
Range("F2:HP2").NumberFormat = "dd-mmm-yy"
On Error GoTo NoDate
Range("F2:HP2").Find(Format(Now(), "dd-mmm-yy"), , xlValues).Select
Exit Sub
NoDate:
MsgBox Format(Now(), "dd-mmm-yy") & " was not found."
End Sub

Note that this will also fail if the columns are not wide enough to show the date - if they have
######## in them, increase the column width.

HTH,
Bernie
MS Excel MVP