# Fill column with dates of month depending on month in A1

1. ## Fill column with dates of month depending on month in A1

I'm trying to figure out the following. A1 contains first of month and
is formated as Mmmm yyyy, A6 is =A1 and A7=A6+1 and so on formated as
mm/dd/yyyy. I want to programatically fill column A with the rest of
the days of the month when the user changes A1. I'm using Private Sub
Worksheet_change(ByVal Target As Range) to clear the old days and then
copy A7 down for the remainder of month. Can't figure out how to make
it stop at the end of the month. Thanks in advance for any help.

2. ## Re: Fill column with dates of month depending on month in A1

On 10 Mar 2005 07:09:07 -0800, jashburn13@charter.net wrote:

>I'm trying to figure out the following. A1 contains first of month and
>is formated as Mmmm yyyy, A6 is =A1 and A7=A6+1 and so on formated as
>mm/dd/yyyy. I want to programatically fill column A with the rest of
>the days of the month when the user changes A1. I'm using Private Sub
>Worksheet_change(ByVal Target As Range) to clear the old days and then
>copy A7 down for the remainder of month. Can't figure out how to make
>it stop at the end of the month. Thanks in advance for any help.

A2: =IF(A1="","",IF(MONTH(A1)=MONTH(A1+1),A1+1,""))

and copy/drag down to A31

--ron

3. ## Re: Fill column with dates of month depending on month in A1

try testing this and then change to suit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "\$D\$1" Then Exit Sub
x = Day(DateSerial(Year([d1]), Month([d1]) + 1, 1) - 1) - Day([d1]) + 1
Range("d2:d" & Range("d2").End(xlDown).Row).ClearContents
Set myrng = Range("d2:d" & x)
myrng.Formula = "=d1+1"
myrng.Value = myrng.Value
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
<jashburn13@charter.net> wrote in message
> I'm trying to figure out the following. A1 contains first of month and
> is formated as Mmmm yyyy, A6 is =A1 and A7=A6+1 and so on formated as
> mm/dd/yyyy. I want to programatically fill column A with the rest of
> the days of the month when the user changes A1. I'm using Private Sub
> Worksheet_change(ByVal Target As Range) to clear the old days and then
> copy A7 down for the remainder of month. Can't figure out how to make
> it stop at the end of the month. Thanks in advance for any help.
>

4. ## Re: Fill column with dates of month depending on month in A1

Ron's formula is better than mine so use this instead.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "\$D\$1" Then Exit Sub
Range("d2:d" & Range("d2").End(xlDown).Row).ClearContents
Set myrng = Range("d2:d32")
myrng.Formula = "=IF(d1="""","""",IF(MONTH(d1)=MONTH(d1+1),d1+1,""""))"
myrng.Value = myrng.Value
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"Don Guillett" <donaldb@281.com> wrote in message
news:e6AFdeYJFHA.1308@TK2MSFTNGP15.phx.gbl...
> try testing this and then change to suit
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address <> "\$D\$1" Then Exit Sub
> x = Day(DateSerial(Year([d1]), Month([d1]) + 1, 1) - 1) - Day([d1]) + 1
> Range("d2:d" & Range("d2").End(xlDown).Row).ClearContents
> Set myrng = Range("d2:d" & x)
> myrng.Formula = "=d1+1"
> myrng.Value = myrng.Value
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> <jashburn13@charter.net> wrote in message
> > I'm trying to figure out the following. A1 contains first of month and
> > is formated as Mmmm yyyy, A6 is =A1 and A7=A6+1 and so on formated as
> > mm/dd/yyyy. I want to programatically fill column A with the rest of
> > the days of the month when the user changes A1. I'm using Private Sub
> > Worksheet_change(ByVal Target As Range) to clear the old days and then
> > copy A7 down for the remainder of month. Can't figure out how to make
> > it stop at the end of the month. Thanks in advance for any help.
> >

>
>

5. ## Re: Fill column with dates of month depending on month in A1

Or for d7 down instead of d2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "\$D\$1" Then Exit Sub
Range("d7:d" & Range("d7").End(xlDown).Row).ClearContents
Range("d7") = Range("d1") + 1
Set myrng = Range("d8:d40")
myrng.Formula = "=IF(d7="""","""",IF(MONTH(d7)=MONTH(d7+1),d7+1,""""))"
myrng.Value = myrng.Value
End Sub

--
Don Guillett
SalesAid Software
donaldb@281.com
"Don Guillett" <donaldb@281.com> wrote in message
news:e6AFdeYJFHA.1308@TK2MSFTNGP15.phx.gbl...
> try testing this and then change to suit
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address <> "\$D\$1" Then Exit Sub
> x = Day(DateSerial(Year([d1]), Month([d1]) + 1, 1) - 1) - Day([d1]) + 1
> Range("d2:d" & Range("d2").End(xlDown).Row).ClearContents
> Set myrng = Range("d2:d" & x)
> myrng.Formula = "=d1+1"
> myrng.Value = myrng.Value
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> <jashburn13@charter.net> wrote in message
> > I'm trying to figure out the following. A1 contains first of month and
> > is formated as Mmmm yyyy, A6 is =A1 and A7=A6+1 and so on formated as
> > mm/dd/yyyy. I want to programatically fill column A with the rest of
> > the days of the month when the user changes A1. I'm using Private Sub
> > Worksheet_change(ByVal Target As Range) to clear the old days and then
> > copy A7 down for the remainder of month. Can't figure out how to make
> > it stop at the end of the month. Thanks in advance for any help.
> >

>
>

6. ## Re: Fill column with dates of month depending on month in A1

On Thu, 10 Mar 2005 10:28:07 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On 10 Mar 2005 07:09:07 -0800, jashburn13@charter.net wrote:
>
>>I'm trying to figure out the following. A1 contains first of month and
>>is formated as Mmmm yyyy, A6 is =A1 and A7=A6+1 and so on formated as
>>mm/dd/yyyy. I want to programatically fill column A with the rest of
>>the days of the month when the user changes A1. I'm using Private Sub
>>Worksheet_change(ByVal Target As Range) to clear the old days and then
>>copy A7 down for the remainder of month. Can't figure out how to make
>>it stop at the end of the month. Thanks in advance for any help.

>
>
>A2: =IF(A1="","",IF(MONTH(A1)=MONTH(A1+1),A1+1,""))
>
>and copy/drag down to A31
>
>
>--ron

Hmmm, missed the part about wanting to do this programmatically.

Try this:

=======================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim aoi As Range
Const DateFormat As String = "dd mmm yyyy" 'or whatever

Set aoi = Range("A7:A36")

If Not Intersect(Target, [A1]) Is Nothing Then
If IsDate([A1].Value) Then
aoi.Clear
Range("A6").Value = Range("A1").Value
Range("A6").NumberFormat = DateFormat

For Each c In aoi
If Day(c.Offset(-1, 0).Value + 1) > _
Day(c.Offset(-1, 0).Value) And _
c.Offset(-1, 0).Value > 0 Then
c.Value = c.Offset(-1, 0).Value + 1
c.NumberFormat = DateFormat
Else
c.Value = ""
End If
Next c
End If
End If

End Sub
============================

--ron

7. ## Re: Fill column with dates of month depending on month in A1

Ron Rosenfeld wrote:
> On Thu, 10 Mar 2005 10:28:07 -0500, Ron Rosenfeld

<ronrosenfeld@nospam.org>
> wrote:
>
> >On 10 Mar 2005 07:09:07 -0800, jashburn13@charter.net wrote:
> >
> >>I'm trying to figure out the following. A1 contains first of month

and
> >>is formated as Mmmm yyyy, A6 is =A1 and A7=A6+1 and so on formated

as
> >>mm/dd/yyyy. I want to programatically fill column A with the rest

of
> >>the days of the month when the user changes A1. I'm using Private

Sub
> >>Worksheet_change(ByVal Target As Range) to clear the old days and

then
> >>copy A7 down for the remainder of month. Can't figure out how to

make
> >>it stop at the end of the month. Thanks in advance for any help.

> >
> >
> >A2: =IF(A1="","",IF(MONTH(A1)=MONTH(A1+1),A1+1,""))
> >
> >and copy/drag down to A31
> >
> >
> >--ron

>
> Hmmm, missed the part about wanting to do this programmatically.
>
> Try this:
>
> =======================================
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim c As Range
> Dim aoi As Range
> Const DateFormat As String = "dd mmm yyyy" 'or whatever
>
> Set aoi = Range("A7:A36")
>
> If Not Intersect(Target, [A1]) Is Nothing Then
> If IsDate([A1].Value) Then
> aoi.Clear
> Range("A6").Value = Range("A1").Value
> Range("A6").NumberFormat = DateFormat
>
> For Each c In aoi
> If Day(c.Offset(-1, 0).Value + 1) > _
> Day(c.Offset(-1, 0).Value) And _
> c.Offset(-1, 0).Value > 0 Then
> c.Value = c.Offset(-1, 0).Value + 1
> c.NumberFormat = DateFormat
> Else
> c.Value = ""
> End If
> Next c
> End If
> End If
>
> End Sub
> ============================
>
>
> --ron

Thanks Ron! Just what I was looking for!

8. ## Re: Fill column with dates of month depending on month in A1

On 10 Mar 2005 13:20:53 -0800, "jashburn13" <jashburn13@charter.net> wrote:

>Thanks Ron! Just what I was looking for!

--ron

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1