Search code examples
vbaexcelexcel-2010excel-2007

How to one date per row for all dates between 2 dates?


I know this question seem complicated but what I want to do is simple, I got 2 columns:

  • I is my Starting Date
  • L is my ending date
  • G is where all the dates are supposed to be

What I want to do is get the number of days per period (EndDate - StartDate + 1), add this many rows and change value of G to be written day per day.

I already coded the part below, but it doesn't seem to be right:

Sub Dates()
Dim LastRow As Long
Dim addrows
Dim FindDates
Dim CountDays
Dim dddays
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim ir As Long
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

With Sheets("Sheet1")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
End With
addrows = 2

For ir = 1 To LastRow
    FindDates = ws.Range("I" & addrows).Value
    CountDays = ws.Range("L" & addrows).Value - ws.Range("I" & addrows).Value + 1
    Adddays = 0

    For i = 1 To CountDays
        ws2.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ws2.Range("A2").Value = Sheets("Sheet1").Range("A" & addrows).Value
        ws2.Range("C2").Value = FindDates + Adddays
        ws2.Range("C2").Value = ws.Range("G" & addrows).Value
        Adddays = Adddays + 1
    Next i
        addrows = addrows + 1

Next ir

End Sub

File looks as follows: enter image description here

Can you help me a bit? "ws2.Range("C2").Value = FindDates + Adddays" is giving me an error 13


Solution

    • I is my Starting Date
    • L is my ending date
    • G is where all the dates are supposed to be

    What I want to do is get the number of days per period (EndDate - StartDate + 1), add this many rows and change value of G to be written day per day.

    for what above this should help you:

    Sub Dates()
        Dim ir As Long, countDays As Long
    
        With Sheets("Sheet1")
            For ir = .Cells(.Rows.Count, "I").End(xlUp).row To 2 Step -1
                With .Rows(ir)
                    countDays = .range("L1") - .range("I1") + 1
                    If countDays > 1 Then
                        .Offset(1).Resize(countDays - 1).Insert xlDown
                        .Offset(1).Resize(countDays - 1).value = .value
                        With .Resize(countDays).Columns("G")
                            .FormulaR1C1 = "=RC9+ROW()-" & .Rows(1).row
                            .value = .value
                        End With
                    End If                        
                End With
            Next
        End With
    End Sub