I would like to use the today function as a starting point and scrolling down
list the previous 70 weekdays, (25-7-2009 format), in a way that every time
the workbook is opened it is automatically done.

Many thanks

Stephen Christian

On Fri, 24 Jul 2009 17:52:02 -0700, Stephen Christian <Stephen
wrote:

I would like to use the today function as a starting point and scrolling down
list the previous 70 weekdays, (25-7-2009 format), in a way that every time
the workbook is opened it is automatically done.

Many thanks

Stephen Christian

One method:

A1: =TODAY()
A2: =WORKDAY(A1,-1)

Fill down to A71

If you get a #NAME error, look at HELP for the WORKDAY function to see how to
fix it.

--ron

Hi,

What does your data look like? Also do you want to move down to the current
date row or do you want to display the previous 70 weekday dates? And how is
this laid out? - with today at the top and the previous dates below or
something like

5/2/2009
5/3/2009
....
7/24/2009
7/25/2009
....

or
7/25/2009
7/24/2009
7/23/2009
....

If the dates are already in the cells and you just want to move to today's
date you will need a macro:

Sub FindDate()
Dim c, myCell
With Worksheets(1).Range("a1:a500")
Set c = .Find(Date, LookIn:=xlValues)
If Not c Is Nothing Then
Range(c.Address).Select
End If
End With
End Sub

This code is added to the thisWorkbook object in the VBA editor by pressing
Alt+F11 and double-clicking the thisWorkbook object listed under your
filename in the top left corner (project explorer window)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Stephen Christian" wrote:

I would like to use the today function as a starting point and scrolling down
list the previous 70 weekdays, (25-7-2009 format), in a way that every time
the workbook is opened it is automatically done.

Many thanks

Stephen Christian

Two minor modifications:

Private Sub Workbook_Open()
Dim c
Worksheets(1).Activate
With Worksheets(1).Range("a1:a500")
Set c = .Find(Date, LookIn:=xlValues)
If Not c Is Nothing Then
Range(c.Address).Select
End If
End With
End Sub

would have worked in the last example if you were on the first sheet in the
workbook and that was where the dates were.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Stephen Christian" wrote:

I would like to use the today function as a starting point and scrolling down
list the previous 70 weekdays, (25-7-2009 format), in a way that every time
the workbook is opened it is automatically done.

Many thanks

Stephen Christian