Replicate amount over time based on date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have need to chart data over a range of dates, each data point would be a
month, the chart may last for two years
For a particular project
I will calculate a number say X
I will have start month, and duration in months
I want to show on a chart X for the period start month to start month +
duration
For instance if the amount is 400
and the start date is 7/07, and the duration is 3

I need 400 to be in a sequence of three cells, for 7/07, 8/07, and 9/07

Would anyone have an idea on how to do this ?
Thanks
 
I assumed each month had the same number of days

Sub makechart()

StartMonth = 7
StartYear = 2007
NumberOfMonths = 10
MyAmount = 345

MyDate = DateSerial(StartYear, StartMonth, 1)
AmountPerMonth = MyAmount / NumberOfMonths

StartRow = 1
StartColumn = "A"

For LoopCounter = 0 To (NumberOfMonths - 1)

MyDate = DateSerial(StartYear, StartMonth + LoopCounter, 1)
Cells(StartRow, StartColumn).Offset(0, LoopCounter) = MyDate
Cells(StartRow, StartColumn).Offset(1, LoopCounter) = _
LoopCounter * AmountPerMonth

Next LoopCounter

End Sub
 
Code has small problem. Last month doesn't have total amount. Not sure how
you want the data. Example below with 3 month and amount 400

case #1 - first month sttarts with 0
0 200 400

change from:
AmountPerMonth = MyAmount / NumberOfMonths
to
AmountPerMonth = MyAmount / (NumberOfMonths - 1)

case #2 - first month contain amount / months
133 266 400
from:
Cells(StartRow, StartColumn).Offset(1, LoopCounter) = _
LoopCounter * AmountPerMonth
to:
Cells(StartRow, StartColumn).Offset(1, LoopCounter) = _
( LoopCounter + 1) * AmountPerMonth
 
Back
Top