Dating cells in a worksheet using VBA

I wish to use VBA to automatically date selected cells.The dates are in
ascending order and are separated by a fixed number of days.

For example :-
Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan 2006
, 22 Jan 2006.

My experience in VBA is almost non-existent in this area and I would
appreciate some help on this matter.

Any Suggestions please ?
--
FLYNNE

nDate = DateValue(2006,1,1)

With ActiveSheet
.Range("A1").Value = nDate
.Range("A10").Value = nDate + 7
.Range("A20").Value = nDate + 14
.Range("A30").Value = nDate + 21
End With

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"FLYNNE" <[email protected]> wrote in message
news:[email protected]...
> I wish to use VBA to automatically date selected cells.The dates are in
> ascending order and are separated by a fixed number of days.
>
> For example :-
> Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan
2006
> , 22 Jan 2006.
>
> My experience in VBA is almost non-existent in this area and I would
> appreciate some help on this matter.
>
> Any Suggestions please ?
> --
> FLYNNE

Thanks Bob for your rapid replys.

I wish to clarify what I am tring to do..

In essence I wish to run VBA code to accomplish the following :-

1. To copy a details box ( a table containg Name ,Date and other details)
for each day of the year i.e obtain 365 identical boxes except for the date.

This I have already accomplished by running the following code.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/07/2006 by j.winney
'

'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Range("A1").Select
For t = 1 To 13140 Step 36
Selection.CurrentRegion.Select
Selection.Copy
Range("A37")(t).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next t
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

2. I want to insert the date for each box concurrently automatically using
VBA viz just like a diary or calendar.
This is the bit I am unable to do at present.

I hope these details make things a bit clearer.

Regards

--
FLYNNE


"Bob Phillips" wrote:

> nDate = DateValue(2006,1,1)
>
> With ActiveSheet
> .Range("A1").Value = nDate
> .Range("A10").Value = nDate + 7
> .Range("A20").Value = nDate + 14
> .Range("A30").Value = nDate + 21
> End With
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "FLYNNE" <[email protected]> wrote in message
> news:[email protected]...
> > I wish to use VBA to automatically date selected cells.The dates are in
> > ascending order and are separated by a fixed number of days.
> >
> > For example :-
> > Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan
> 2006
> > , 22 Jan 2006.
> >
> > My experience in VBA is almost non-existent in this area and I would
> > appreciate some help on this matter.
> >
> > Any Suggestions please ?
> > --
> > FLYNNE
>
>
>

Hello Bob

I tried your chunk of code - it worked perfectly - Thanks.

Is it possible to modify this code (using a loop maybe) so that consecutive
dates can be added to each table in turn.
I would like to send you a copy of the file I am working on.
How does one do this in this newsgroup ?

Regards
--
FLYNNE


"FLYNNE" wrote:

> Thanks Bob for your rapid replys.
>
> I wish to clarify what I am tring to do..
>
> In essence I wish to run VBA code to accomplish the following :-
>
> 1. To copy a details box ( a table containg Name ,Date and other details)
> for each day of the year i.e obtain 365 identical boxes except for the date.
>
> This I have already accomplished by running the following code.
>
> Sub Macro2()
> '
> ' Macro2 Macro
> ' Macro recorded 10/07/2006 by j.winney
> '
>
> '
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> Range("A1").Select
> For t = 1 To 13140 Step 36
> Selection.CurrentRegion.Select
> Selection.Copy
> Range("A37")(t).Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Next t
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
> End Sub
>
> 2. I want to insert the date for each box concurrently automatically using
> VBA viz just like a diary or calendar.
> This is the bit I am unable to do at present.
>
> I hope these details make things a bit clearer.
>
> Regards
>
> --
> FLYNNE
>
>
> "Bob Phillips" wrote:
>
> > nDate = DateValue(2006,1,1)
> >
> > With ActiveSheet
> > .Range("A1").Value = nDate
> > .Range("A10").Value = nDate + 7
> > .Range("A20").Value = nDate + 14
> > .Range("A30").Value = nDate + 21
> > End With
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "FLYNNE" <[email protected]> wrote in message
> > news:[email protected]...
> > > I wish to use VBA to automatically date selected cells.The dates are in
> > > ascending order and are separated by a fixed number of days.
> > >
> > > For example :-
> > > Date cells A1 , A10 , A20 , A30 with 1 Jan 2006 , 8 Jan 2006 , 15 Jan
> > 2006
> > > , 22 Jan 2006.
> > >
> > > My experience in VBA is almost non-existent in this area and I would
> > > appreciate some help on this matter.
> > >
> > > Any Suggestions please ?
> > > --
> > > FLYNNE
> >
> >
> >