Last Day of the Latest Month Syntax problem

[u473]

From a column of sorted dates in A
I need to generate the Month columns headers in the YYMM format,
First : I need to find the Earliest and Latest dates in Column A
Second : From the above, I need retrieve the Last day of the Latest
Month.
I have a syntax problem there in assigning date to
variable and variable to cell

'
'Sub MonthCostDistrib()
Dim LastRow As Long
Dim rDate As Range
Dim dtMax As Date, dtMin As Date
Dim startdate As Date, enddate As Date
LastRow = Cells(65536, 1).End(xlUp).Row
x = 3
Set rDate = Range("A3:A" & LastRow)
'.................................
'Find Start & End Dates
'.................................
dtMin = Application.Min(rDate)
dtMax = Application.Max(rDate)
Range("C1").Activate
ActiveCell.Value = dtMin
Range("D1").Activate
ActiveCell.Value = dtMax
' Retrieve Last Day of Month
enddate = Range("D1").Value ' Not appropriate
'Should be something like :
'enddate = Date(Year(D1), Month(D1) + 1, 0)
'......................................................
'Fill Calendar columns Months Headers
'......................................................
Do
With Cells(2, x)
.Value = startdate
.NumberFormat = "mmyy"
End With
startdate = DateAdd("m", 1, startdate)
x = x + 1
Loop Until startdate > enddate
End Sub
'
Thank you for your help
J.P.

 

[RadarEye]

From a column of sorted dates in A
I need to generate the Month columns headers in  the YYMM format,
First : I need to find the Earliest and Latest dates in Column A
Second : From the above, I need retrieve the Last day of the Latest
Month.
              I have a syntax problem there in assigning date to
variable and variable to cell

'
'Sub MonthCostDistrib()
Dim LastRow As Long
Dim rDate As Range
Dim dtMax As Date, dtMin As Date
Dim startdate As Date, enddate As Date
LastRow = Cells(65536, 1).End(xlUp).Row
x = 3
Set rDate = Range("A3:A" & LastRow)
'.................................
'Find Start & End Dates
'.................................
dtMin = Application.Min(rDate)
dtMax = Application.Max(rDate)
Range("C1").Activate
ActiveCell.Value = dtMin
Range("D1").Activate
ActiveCell.Value = dtMax
' Retrieve Last Day of Month
enddate = Range("D1").Value ' Not appropriate
'Should be something like :
'enddate = Date(Year(D1), Month(D1) + 1, 0)
'......................................................
'Fill Calendar columns Months Headers
'......................................................
Do
    With Cells(2, x)
        .Value = startdate
        .NumberFormat = "mmyy"
    End With
    startdate = DateAdd("m", 1, startdate)
    x = x + 1
Loop Until startdate > enddate
End Sub
'
Thank you for your help
J.P.

Hi J.P.,

You are close to the solution.
just add thes lines before the fill calander part

startdate = DateSerial(Year(dtMin), Month(dtMin), 1)
enddate = DateSerial(Year(dtMax), Month(dtMax), 1)
enddate = DateAdd("d", -1, DateAdd("m", 1, enddate))

HTH,

Wouter

 

[Mike H]

Hi,

The whole thing can be simlified like this

Sub MonthCostDistrib()
Dim LastRow As Long
Dim rDate As Range
Dim startdate As Date, enddate As Date
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
x = 3

Set rDate = Range("A3:A" & LastRow)

startdate = Application.Min(rDate)
enddate = DateSerial(Year(Application.Max(rDate)), _
Month(Application.Max(rDate)) + 1, 0)
'......................................................
'Fill Calendar columns Months Headers
'......................................................
Do
With Cells(2, x)
.Value = startdate
.NumberFormat = "mmyy"
End With
startdate = DateAdd("m", 1, startdate)
x = x + 1
Loop Until startdate > enddate
End Sub

Mike

 

[u473]

Wooowww !!! Smart ! Thank you.
J.P.

 

[Rick Rothstein]

Or, without a loop...

Sub MonthCostDistrib()
Dim rDate As Range
Dim StartDate As Date
Dim MonthCount As Long
Set rDate = Range("A3", Cells(Rows.Count, "A").End(xlUp))
StartDate = WorksheetFunction.Min(rDate)
MonthCount = DateDiff("m", StartDate, WorksheetFunction.Max(rDate)) + 1
'......................................................
'Fill Calendar columns Months Headers
'......................................................
With Range("B3")
.Value = StartDate
.Resize(MonthCount).NumberFormat = "mmyy"
If MonthCount > 1 Then .AutoFill .Resize(, MonthCount), xlFillMonths
End With
End Sub

 

[Mike H]

Rick,

Nice. I never considered resize because the last bit of the code was
something I wrote last week for the OP when the OP asked for a loop.

Mike