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. |
Fill column with dates of month depending on month in A1
|
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. |
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. |
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 |
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! |
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 |
All times are GMT +1. The time now is 12:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com