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