How to create a row list according with dates

Solved/Closed
ruidelgadoalves Posts 5 Registration date Thursday January 26, 2012 Status Member Last seen March 5, 2012 - Jan 26, 2012 at 04:45 AM
ruidelgadoalves Posts 5 Registration date Thursday January 26, 2012 Status Member Last seen March 5, 2012 - Jan 27, 2012 at 03:11 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 26, 2012 at 07:46 AM
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
0
ruidelgadoalves Posts 5 Registration date Thursday January 26, 2012 Status Member Last seen March 5, 2012
Jan 27, 2012 at 03:11 AM
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 26, 2012 at 07:47 AM
the main data is in sheet1 and result in sheet 2
0