Create an array of separate cells
On Wed, 21 Mar 2012 20:12:29 -0700 (PDT), Rickemil wrote:
Hi all,
I would like to know how I can create an array of 1 column with cells
that are not in order, that contains the data that is in cell A1, C3,
and E5. And use that array created in the formula FVSCHEDULE.
Something like this: "FVSCHEDULE (principal, MyArray (A1, C3, E5))"
Please excuse my English.
You can do this with a simple User Defined Function:
To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=FVSCHEDULE(principal,MyArray(A1,C3,E5))
in some cell.
======================================
Option Explicit
Function MyArray(ParamArray Schedule() As Variant) As Variant
MyArray = Schedule
End Function
===========================
|