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

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

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

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

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