|
Hello,
I am currently trying to create a macro, that calculates the NPV of an unknown number of projects with an unknown amount of cash flows. The projects are listed in an excel file, starting in column E. Number of projects and number of cashflows is variable. Which is why I use rowend and colend to find out how big the actual table with input data is. Then I stored the cash flows in an 2 dimensional array. All of that worked. But now I would like to calculate the NPV for the cash flows stored in NPVarray(0, 0 to colend), then NPVarray(1, 0 to colend), NPVarray(2, 0 to colend) ... and so on.
Idealy the outputs of this calculation are stored in a second one dimensional array.
Appreciate any kind of help. I appologise for the chaotic code, I am still at the beginner stage of coding.
Here is my code so far: It gives a syntax error for the NPVarray(,index) at the end, but I don't know how to solve it in a different way.
Sub Start_NPV_analysis()
Dim rowend As Long, colend As Long, rowindex As Long, colindex As Long, xrow As Long, xcol As Long
Dim npvval As Double, index As Long
colend = Worksheets("Exercise 3").Cells(3, Columns.Count).End(xlToLeft).Column - 5
rowend = Range("E" & Rows.Count).End(xlUp).Row - 4
Dim NPVarray() As Double
Dim output() As Double
rowindex = 0
colindex = 0
xrow = 4
xcol = 5
index = 0
ReDim NPVarray(rowend, colend)
ReDim output(colend)
'outer loop down rows
Do Until Cells(xrow, xcol).Value = ""
'inner loop across columns
Do Until Cells(xrow, xcol).Value = ""
NPVarray(rowindex, colindex) = Cells(xrow, xcol)
colindex = colindex + 1 'increase array index in 2nd dimension
xcol = xcol + 1 'increases column on worksheet
Loop
xcol = 5 'reset after done with row
colindex = 0 'reset 2nd dimension index in array
xrow = xrow + 1 'increases row on worksheet
rowindex = rowindex + 1 'increases array index in first dimension
Loop
Do Until Cells(xrow, xcol).Value = ""
output(index) = NPV(Cells(3, xcol), NPVarray(, index))
index = index + 1
xcol = xcol + 1
Loop
End Sub
|