Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger inserting Date (again!)
Rick supplied the following code. It populates col A with dates after I
enter a date (that will always be the first day of a given month). It works great, but now I find myself having to amend it so as rather than entering the "start" date in A1, I have to now insert that start date in A3, then populate the dates from A4 down to A252. I'd appreciate an amendment to Rick's code that allows a date entry in A3 (rather than A1) to populate A4:A252 (rather than A2:A248) with dates for every 3 hrs - 8 date entries for each day of a month. I've tried the obvious, but my dates keep being inserted from A1. Tks Rick Rothstein wrote: This should do it for you... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$1" And IsDate(Target.Value) Then Range("A2:A248").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A1").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A1").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger inserting Date (again!)
Hi
Have you change A1 to A3 throughout the code? Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$3" And IsDate(Target.Value) Then Range("A4:A252").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A3").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A3").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub Regards, Per "Ricky" skrev i meddelelsen ... Rick supplied the following code. It populates col A with dates after I enter a date (that will always be the first day of a given month). It works great, but now I find myself having to amend it so as rather than entering the "start" date in A1, I have to now insert that start date in A3, then populate the dates from A4 down to A252. I'd appreciate an amendment to Rick's code that allows a date entry in A3 (rather than A1) to populate A4:A252 (rather than A2:A248) with dates for every 3 hrs - 8 date entries for each day of a month. I've tried the obvious, but my dates keep being inserted from A1. Tks Rick Rothstein wrote: This should do it for you... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$1" And IsDate(Target.Value) Then Range("A2:A248").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A1").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A1").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger inserting Date (again!)
Yep, but it populates from A1 after I enter the start date in A3, rather
than A4. Cheers, Ricky Per Jessen wrote: Hi Have you change A1 to A3 throughout the code? Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$3" And IsDate(Target.Value) Then Range("A4:A252").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A3").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A3").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub Regards, Per "Ricky" skrev i meddelelsen ... Rick supplied the following code. It populates col A with dates after I enter a date (that will always be the first day of a given month). It works great, but now I find myself having to amend it so as rather than entering the "start" date in A1, I have to now insert that start date in A3, then populate the dates from A4 down to A252. I'd appreciate an amendment to Rick's code that allows a date entry in A3 (rather than A1) to populate A4:A252 (rather than A2:A248) with dates for every 3 hrs - 8 date entries for each day of a month. I've tried the obvious, but my dates keep being inserted from A1. Tks Rick Rothstein wrote: This should do it for you... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$1" And IsDate(Target.Value) Then Range("A2:A248").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A1").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A1").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger inserting Date (again!)
I see the problem, change from this:
With Cells(X , "A") to this: With Cells(X + 3, "A") Regards, Per "Ricky" skrev i meddelelsen . au... Yep, but it populates from A1 after I enter the start date in A3, rather than A4. Cheers, Ricky Per Jessen wrote: Hi Have you change A1 to A3 throughout the code? Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$3" And IsDate(Target.Value) Then Range("A4:A252").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A3").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A3").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub Regards, Per "Ricky" skrev i meddelelsen ... Rick supplied the following code. It populates col A with dates after I enter a date (that will always be the first day of a given month). It works great, but now I find myself having to amend it so as rather than entering the "start" date in A1, I have to now insert that start date in A3, then populate the dates from A4 down to A252. I'd appreciate an amendment to Rick's code that allows a date entry in A3 (rather than A1) to populate A4:A252 (rather than A2:A248) with dates for every 3 hrs - 8 date entries for each day of a month. I've tried the obvious, but my dates keep being inserted from A1. Tks Rick Rothstein wrote: This should do it for you... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$1" And IsDate(Target.Value) Then Range("A2:A248").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A1").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A1").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger inserting Date (again!)
Thanks Per, that did the trick!
Cheers, Ricky Per Jessen wrote: I see the problem, change from this: With Cells(X , "A") to this: With Cells(X + 3, "A") Regards, Per "Ricky" skrev i meddelelsen . au... Yep, but it populates from A1 after I enter the start date in A3, rather than A4. Cheers, Ricky Per Jessen wrote: Hi Have you change A1 to A3 throughout the code? Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$3" And IsDate(Target.Value) Then Range("A4:A252").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A3").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A3").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub Regards, Per "Ricky" skrev i meddelelsen ... Rick supplied the following code. It populates col A with dates after I enter a date (that will always be the first day of a given month). It works great, but now I find myself having to amend it so as rather than entering the "start" date in A1, I have to now insert that start date in A3, then populate the dates from A4 down to A252. I'd appreciate an amendment to Rick's code that allows a date entry in A3 (rather than A1) to populate A4:A252 (rather than A2:A248) with dates for every 3 hrs - 8 date entries for each day of a month. I've tried the obvious, but my dates keep being inserted from A1. Tks Rick Rothstein wrote: This should do it for you... Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long If Target.Address = "$A$1" And IsDate(Target.Value) Then Range("A2:A248").Clear If Day(Target.Value) = 1 Then For X = 1 To 8 * Day(DateAdd("m", 1, Range("A1").Text) - 1) Step 8 With Cells(X, "A") .Resize(8, 1).Value = Range("A1").Value + Int(X / 8) With .Offset(7).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End With Next End If End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trigger inserting date | Excel Programming | |||
Using date as trigger for macro | Excel Discussion (Misc queries) | |||
Inserting rows on cell trigger | Excel Programming | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
Date Flag/Trigger | Excel Programming |