Distribute Cost by Month
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. |
All times are GMT +1. The time now is 03:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com