U
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.
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.