Using an arrays to aggregate.

  • Thread starter Thread starter tcucinotta
  • Start date Start date
T

tcucinotta

I am trying to aggregate a series of monthly cashflows using an array.
In my output I am only getting the very first run of cash flows (r=1)
as oppose to the total of the 5 runs. This needs to be done in VBA as
oppose to on a spreadsheet as ultimately this will be run 1,000 times
instead of 5.

Any thoughts?

Code Below

' Cash Flow aggregation

For r = 1 To 5
....
For i = 1 To 720
Term_CF(i) = Sheet4.Range("Monthly_Term").Cells(i,
1).Value
Term_Total(i) = Term_Total(i) + Term_CF(i)
MIP_CF(i) = Sheet4.Range("Monthly_MIP").Cells(i, 1).Value
MIP_Total(i) = MIP_Total(i) + MIP_CF(i)
Next i

Next r

For i = 1 To 720
Sheet10.Range("CashFlow_Term").Cells(i, 1).Value =
Val(Term_Total(i))
Sheet10.Range("CashFlow_MIP").Cells(i, 1).Value =
Val(MIP_Total(i))
Next i
 
I think I need to se miore of your code to answer this - I can see where you
are dimming your arrays etc.

Also I am a bit lost as to why you have the "r" loop as r doesnt seem to be
reference anywhere within the loop (just one of those test harness things I
guess?)


ttfn benm
 
Assume your data is in A1:E720 in sheet Monthly_Term then

Code Below

' Cash Flow aggregation

For r = 1 To 5
....
For i = 1 To 720
Term_CF(i) = Sheet4.Range("Monthly_Term").Cells(i,r).Value
Term_Total(i) = Term_Total(i) + Term_CF(i)
MIP_CF(i) = Sheet4.Range("Monthly_MIP").Cells(i, r).Value
MIP_Total(i) = MIP_Total(i) + MIP_CF(i)
Next i

Next r

For i = 1 To 720
Sheet10.Range("CashFlow_Term").Cells(i, 1).Value =
Val(Term_Total(i))
Sheet10.Range("CashFlow_MIP").Cells(i, 1).Value =
Val(MIP_Total(i))
Next i
 
Back
Top