How to create a row list according with dates Solved/Closed

Question

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,

venkat1926 · Answer

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

venkat1926 · Answer

the main data is in sheet1 and result in sheet 2

How to create a row list according with dates

2 responses

Similar discussions

Subscribe To Our Newsletter!