Array of month end dates between 2 dates

Question

In my worksheet, I have 2 cells that serve to denote the minimum date and maximum date. Also in the worksheet, I have a table structure. What I'd like to achieve, is to have extra columns appended to the end of the table showing each month in between those 2 dates, inclusive.

For example, minimum date is 7/31/2014 and maximum is 6/30/2015. I would like a macro to populate the column headers 7/31/2014, 8/31/2014, ..., 6/30/2015 to the end of my table.

Unfortunately, Excel tables can't have dynamic headers. I then thought of using VBA by having an array of dates, and then setting the Range.Value to the array, but couldn't quite figure out how to code it.

Thank you.

Answer 1

You want DateAdd()

Assuming you want to get the last date of every month you should instead use DateSerial()

Public Sub test()
    Dim startDate As Date
    Dim endDate As Date
    Dim currentDate As Date

    startDate = CDate("7/31/2014")
    endDate = CDate("6/30/2015")

    currentDate = startDate
    Do While currentDate <= endDate
        MsgBox currentDate

        'currentDate = DateAdd("m", 1, currentDate)
        currentDate = DateSerial(Year(currentDate), Month(currentDate) + 2, 0)
    Loop
End Sub

Answer 2

You don't really need VBA. A pretty simple Excel formula will do the trick.

In the example below, cell C5 has =$C$2. Cell C6 has this formula:

=IF(C5>=$C$2,"",DATE(YEAR(C5),MONTH(C5)+2,DAY(0)))

and, for the purposes of this example, it is copied down to cell C23. You would just have to copy it as far down as you would need in your longest conceivable table.

Explanation: the formula adds 2 months to the previous date, but then takes "day 0" of that month which is equivalent the last day of the month before (which I think is what you want based on your example). If the previous date has reached the max, then it just writes an empty string "" from then on.

Maybe you want this in a row, not a column; the idea is the same.

Answer 3

Here is a small example based on:

Here is the code:

Sub MAIN()
    Dim d1 As Date, d2 As Date, Tbl As Range
    d1 = Range("A1").Value
    d2 = Range("A2").Value
    Set Tbl = Range("B3:E9")
    Call setLabels(d1, d2, Tbl)
End Sub

Sub setLabels(dt1 As Date, dt2 As Date, rng As Range)
    Dim rToFill As Range, r As Range
    Set rToFill = Intersect(rng(1).EntireRow, rng).Offset(-1, 0)
    For Each r In rToFill
        dv = dt1 + i
        r.Value = dv
        i = i + 1
        If dt1 + i > dt2 Then Exit Sub
    Next r
End Sub

This is based on 1 day increments. If you want 1 month increments, then use this for dv

dv = DateSerial(Year(dt1), Month(dt1) + i, Day(dt1))

Answer 4

VBA has functions that handle dates. If you look at the link:

http://software-solutions-online.com/2014/02/21/excel-vba-working-with-dates/

It will show you how to make variables of data type Date using VBA, which will make adding and subtracting months very easy for you.

After that, add methods to create the columns you want and the .Name property to name the columns.