How to create a row list according with dates

[Solved/Closed]
Report
Posts
5
Registration date
Thursday January 26, 2012
Status
Member
Last seen
March 5, 2012
-
Posts
5
Registration date
Thursday January 26, 2012
Status
Member
Last seen
March 5, 2012
-
Hello,

I'm trying to create a list of dates according with the time periods shown bellow on each row, i.e. for entry 1991001, I need to replicate that entry on a column and get 22, 23 and 24-07-1991 in adjacent cells. I must do this for every entry of the first column on a sequence way. the last column is the countdown of sequences needed.

1991001 22-07-1991 24-07-1991 3
1991002 17-06-1991 17-07-1991 31
1991003 23-07-1991 29-07-1991 7
1991004 24-07-1991 29-07-1991 6
1991005 24-07-1991 30-07-1991 7

Result to get

1991001 22-07-1991
1991001 23-07-1991
1991001 24-07-1991
1991002 17-06-1991
1991002 18-06-1991
...
...

Would you be so kind to help me on this?

Best regards,

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
your main data is from A1 to D5 (it can have more rows)

try this macro "test"

Sub test()
Dim r As Range, c As Range, j As Long, item, begdate
Dim r1 As Range, dest As Range
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
Set r = Range(.Range("D1"), .Range("D1").End(xlDown))
For Each c In r
j = c.Value
item = .Cells(c.Row, "A")
Set r1 = .Cells(c.Row, "B")
begdate = Mid(r1, 4, 2) & "/" & Left(r1, 2) & "/" & Right(r1, 2) + 0
'MsgBox begdate
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Range(dest, dest.Offset(j - 1, 0)).FormulaArray = item
dest.Offset(0, 1) = begdate
Range(dest.Offset(0, 1), dest.Offset(j - 1, 1)) _
  .DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
        xlDay, Step:=1, Trend:=False
End With
Next c
End With
Worksheets("sheet2").Activate
Range("a1").Select
End Sub
Posts
5
Registration date
Thursday January 26, 2012
Status
Member
Last seen
March 5, 2012

Many thanks,

It works perfectly. I'm not used to VBA macro programing, but I'm going to dissecate your code, line by line to fully understand it.

Many thanks again. It solved me a lot of hours of work.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
the main data is in sheet1 and result in sheet 2