Insert Columns For Missing Dates

[sugargenius]

I have report that comes out of a legacy system with fiscal period end dates as column headings and budget values below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1/31/2018[/TD]
[TD]3/31/201[/TD]
[TD]4/30/2018[/TD]
[TD]7/31/2018[/TD]
[TD]9/30/2018[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]25000[/TD]
[TD]40000[/TD]
[TD]30000[/TD]
[TD]45000[/TD]
[/TR]
[TR]
[TD]55000[/TD]
[TD]30000[/TD]
[TD]45000[/TD]
[TD]150000[/TD]
[TD]30000[/TD]
[/TR]
[TR]
[TD]40000[/TD]
[TD]45000[/TD]
[TD]40000[/TD]
[TD]25000[/TD]
[TD]50000[/TD]
[/TR]
</tbody>[/TABLE]

I need macro that loops through each period based on a arbitrary start end date and inserts any missing periods in above table.

Using the table above and a start date of 1/31/2018 and a finish date of 12/31/2018, the result would look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1/31/18[/TD]
[TD]2/28/18[/TD]
[TD]3/31/18[/TD]
[TD]4/30/18[/TD]
[TD]5/31/18[/TD]
[TD]6/30/18[/TD]
[TD]7/31/18[/TD]
[TD]8/31/18[/TD]
[TD]9/30/18[/TD]
[TD]10/31/18[/TD]
[TD]11/30/18[/TD]
[TD]12/31/18[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD][/TD]
[TD]25000[/TD]
[TD]40000[/TD]
[TD][/TD]
[TD][/TD]
[TD]30000[/TD]
[TD][/TD]
[TD]45000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55000[/TD]
[TD][/TD]
[TD]30000[/TD]
[TD]45000[/TD]
[TD][/TD]
[TD][/TD]
[TD]150000[/TD]
[TD][/TD]
[TD]30000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40000[/TD]
[TD][/TD]
[TD]45000[/TD]
[TD]40000[/TD]
[TD][/TD]
[TD][/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]50000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here's what I have so far (not working):

Option Base 1
Public Sub datetest()
    Dim startDate As Date
    Dim aPeriods() As Date
    Dim iPeriods As Integer
    Dim rngPeriod As Range
    Dim iStartCol As Integer
    Dim iPeriodRow As Integer
    Dim xyz As Date
    startDate = CDate("1/31/2018")
    endDate = CDate("12/31/2018")
    currentDate = startDate
    iPeriods = 0
    Do While currentDate <= endDate
        iPeriods = iPeriods + 1
        ReDim Preserve aPeriods(iPeriods)
        aPeriods(iPeriods) = currentDate
        currentDate = DateSerial(Year(currentDate), Month(currentDate) + 2, 0)
    Loop
   
    iStartCol = 1
    iPeriodRow = 5
    For i = 1 To iPeriods
        Debug.Print Cells(iPeriodRow, iStartCol + i)
        xyz = CDate(Cells(iPeriodRow, iStartCol + i))
        Debug.Print xyz = aPeriods(i)
        If xyz <> aPeriods(i) Then
            Columns(iStartCol + i).Insert Shift:=xlToLeft
        End If
        
    Next i
    
End Sub

 

[DanteAmor]

Try with this:

Sub Insert_Columns()
    'Insert Columns For Missing Dates
    Application.ScreenUpdating = False
    uc = Cells(5, Columns.Count).End(xlToLeft).Column
    mes = 12
    For j = uc To 1 Step -1
        MDate = DateSerial(Year(Date), mes, Day(DateSerial(Year(Date), mes + 1, 1) - 1))
        If Cells(5, j).Value <> MDate Then
            Columns(j + 1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Cells(5, j + 1).Value = MDate
            j = j + 1
        End If
        mes = mes - 1
        If mes = 0 Then Exit Sub
    Next
    For j = mes To 1 Step -1
        MDate = DateSerial(Year(Date), j, Day(DateSerial(Year(Date), j + 1, 1) - 1))
        Columns(1).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Cells(5, 1).Value = MDate
    Next
End Sub