PC Review


Reply
Thread Tools Rate Thread

Distribute Cost by Month

 
 
u473
Guest
Posts: n/a
 
      26th Dec 2008
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to distribute/spread/map cost of project across different year Dream Microsoft Excel Misc 3 3rd Sep 2009 12:09 PM
How do I distribute cost among group of employees based on salary Denise Microsoft Excel Misc 3 6th May 2009 02:43 PM
Re: Creating Cost Per Month Lynn Trapp Microsoft Access Queries 2 1st Sep 2004 02:01 PM
Re: Creating Cost Per Month Brian Camire Microsoft Access Queries 1 1st Sep 2004 10:33 AM
Re: Creating Cost Per Month Brendan Reynolds Microsoft Access Queries 0 31st Aug 2004 06:06 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:32 AM.