|
Posted to microsoft.public.excel.programming
|
|
|
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
wrote:
On 10 Mar 2005 07:09:07 -0800, 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!
|