Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a series of values
Hi:
I am trying to calculate a Net Present Value of a series of annual rates (30 years). After I get the NPV result, I want to populate the next consecutive cells with the annual rates for over 30 years (when the optional blnOut is true). Here's my code: ----------------------------------------------------- Public Function NPV_LossAdjustmentExp(Optional blnOut As Boolean) As Double Dim arrLossAdjustmentExp() As Double Dim i As Integer ReDim arrLossAdjustmentExp(1 To 30) For i = 1 To 30 arrLossAdjustmentExp(i) = Losses.LossAdjustmentExp(i) Next i NPV_LossAdjustmentExp = NPV(Assumptions.getNPVRate, arrLossAdjustmentExp()) If blnOut Then ActiveCell.Next.Activate For i = 1 To 30 ActiveCell.Value = arrLossAdjustmentExp(i) ActiveCell.Next.Activate Next i End If End Function ---------------------------------------------------- The NPV formula works just fine. But the populate command doesn't work because the cell pointer is still in the same position. In another word, 'ActiveCell.Next.Activate' command can't be executed. Is there a way to execute the NPV calculation, get out from the cell, move to the next cell, populate it, move to the next cell, populate it, and so on. I am not sure if I can do it without completely finishing the function. So, any other logic is welcome. Thank you, Adrian T |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate a series of values
Adrian,
As a general principal, functions can only alter the contents of the cell from which they were called. You therefore can't do this within the function. It might be possible to use Application.Caller and Application.Caller.Parent, respectively the cell and its parent sheet, the array you have created in the function, and a call to a different sub passing these as parameters to populate your other values to the cell on the right and beyond, but I haven't tested it, and it is a dangerous thing to do because the other cells may not be empty. Alternatively, you could use an array formula with a UDF in it, and the function can return an array of values. This is probably far more robust. Use something like this to see if it is an array formula rather than using your blnOut flag. Function Test() As Variant Dim vArray Dim nCounter as Integer nItems = Application.Caller.Cells.Count if nItems = 1 then Test = 1 Else Redim vArray(0 to nItems - 1) For nCounter = 0 to nItems - 1 vArray(nCounter) = nCounter Next nCounter Test = vArray End If End Function Robin Hammond www.enhanceddatasystems.com "Adrian T" wrote in message ... Hi: I am trying to calculate a Net Present Value of a series of annual rates (30 years). After I get the NPV result, I want to populate the next consecutive cells with the annual rates for over 30 years (when the optional blnOut is true). Here's my code: ----------------------------------------------------- Public Function NPV_LossAdjustmentExp(Optional blnOut As Boolean) As Double Dim arrLossAdjustmentExp() As Double Dim i As Integer ReDim arrLossAdjustmentExp(1 To 30) For i = 1 To 30 arrLossAdjustmentExp(i) = Losses.LossAdjustmentExp(i) Next i NPV_LossAdjustmentExp = NPV(Assumptions.getNPVRate, arrLossAdjustmentExp()) If blnOut Then ActiveCell.Next.Activate For i = 1 To 30 ActiveCell.Value = arrLossAdjustmentExp(i) ActiveCell.Next.Activate Next i End If End Function ---------------------------------------------------- The NPV formula works just fine. But the populate command doesn't work because the cell pointer is still in the same position. In another word, 'ActiveCell.Next.Activate' command can't be executed. Is there a way to execute the NPV calculation, get out from the cell, move to the next cell, populate it, move to the next cell, populate it, and so on. I am not sure if I can do it without completely finishing the function. So, any other logic is welcome. Thank you, Adrian T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate y col from x col values | Excel Discussion (Misc queries) | |||
Populate Missing Values | Excel Worksheet Functions | |||
Populate combo box with unique values only | Excel Discussion (Misc queries) | |||
multiple series of values graphed with indep. x values -possible? | Charts and Charting in Excel | |||
Can we make a cell behave like a list box and populate it with values for selection. | Excel Programming |