Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill column with dates of month depending on month in A1
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 wrote in message oups.com... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 "Don Guillett" wrote in message ... 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 wrote in message oups.com... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill column with dates of month depending on month in A1
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 |
#7
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill column with dates of month depending on month in A1
On 10 Mar 2005 13:20:53 -0800, "jashburn13" wrote:
Thanks Ron! Just what I was looking for! You're welcome. Glad to help. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUM of column values depending on the heading (month) | Excel Discussion (Misc queries) | |||
How do I add totals from a range of dates depending on the month? | Excel Worksheet Functions | |||
How do I sort a column of dates by the month? | Excel Discussion (Misc queries) | |||
function to fill all days of month to end of month | Excel Worksheet Functions | |||
function to fill all days of month to end of month | Excel Worksheet Functions |