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.

On 30 dec, 15:06, u473 wrote:
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

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

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" wrote:

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.

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

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
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" wrote:

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.

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

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
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" wrote:

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.