From a column of dates in A, and Cost in B
First : I generate the Month columns headers in the YYMM format,
starting in Column C, row 2.
Slight glitch there, I have to test if the current row
YearMonth of my "A" Dates Column is greater
than the YearMonth of the Last or Max Date
Second : I would like to distribute my Cost Column to the pertaining
months columns, either thru
a populated array or loop. This is where my main problem
is.
'
'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
dtMax = Application.Max(rDate)
dtMin = Application.Min(rDate)
Range("C1").Activate
ActiveCell.Value = dtMin
Range("D1").Activate
ActiveCell.Value = dtMax
startdate = Range("C1").Value
enddate = Range("D1").Value
'
'Fill Calendar Month Headers
Do
With Cells(2, x)
.Value = startdate
.NumberFormat = "mmyy"
End With
startdate = DateAdd("m", 1, startdate)
x = x + 1
'
' Test if YearMonth(startdate) YearMonth(enddate)
Loop Until startdate enddate: ' Test has to be on YearMonth(Dates)
not on Date only
'
'Pseudo-Code
'Loop thru A:B Columns and distribute Cost in Months Columns
' Dim rDateArray(rDate,1) : 'Dim Array of Dates and Cost
' Populate array with YearMonth(rDate) and Cost
' Distribute array in Months Columns
'
End Sub
'
Help appreciated
J.P.