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:

1/31/2018	3/31/201	4/30/2018	7/31/2018	9/30/2018
50000	25000	40000	30000	45000
55000	30000	45000	150000	30000
40000	45000	40000	25000	50000

<tbody>
</tbody>

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:

1/31/18	2/28/18	3/31/18	4/30/18	5/31/18	6/30/18	7/31/18	8/31/18	9/30/18	10/31/18	11/30/18	12/31/18
50000		25000	40000			30000		45000
55000		30000	45000			150000		30000
40000		45000	40000			25000		50000

<tbody>
</tbody>

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