Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XIRR Add-ins
I am looking to reference a named range in a spreadsheet
that has the following columns & rows. This range can expand and contract. I want to have a button on a separate sheet reference the range and display the IRR? Date Deposit/Withdrawal 12/31/2002 -1000 02/08/2002 275 04/25/2002 425 05/21/2002 325 09/13/2002 275 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XIRR Add-ins
I am looking to reference a named range in a spreadsheet
that has the following columns & rows. This range can expand and contract. I want to have a button on a separate sheet reference the range and display the IRR? Date Deposit/Withdrawal 12/31/2002 -1000 02/08/2002 275 04/25/2002 425 05/21/2002 325 09/13/2002 275 I didn't get it to work using a named range, but maybe the following will suffice. Suppose the data will remain on Sheet2 in cells A1:B_ and the CommandButton on Sheet1. Then the following code will put the XIRR in B5 of Sheet1. Private Sub CommandButton1_Click() Dim iRow As Integer iRow = Sheets("Sheet2").Range("B65536").End(xlUp).Row Sheets("Sheet1").Range("B5").FormulaR1C1 = _ "=XIRR(Sheet2!R[-3]C:R[" & iRow - 5 & _ "]C,Sheet2!R[-3]C[-1]:R[" & iRow - 5 & "]C[-1],0.2)" End Sub By the way, I changed 12/31/02 to 12/31/01 to get XIRR to work, and XIRR can't be called directly from VBA. HTH, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
xirr | Excel Worksheet Functions | |||
XIRR | Excel Discussion (Misc queries) | |||
To XIRR or Not To XIRR | Excel Worksheet Functions | |||
Xirr? | Excel Discussion (Misc queries) | |||
XIRR and IRR | Excel Worksheet Functions |