I am trying (unsuccessfully) to dynamically transform a single column
non-contiguous data range to single column contiguous range using VBA,
basically dynamically appending the second range to the first.
My spreadsheet is as follows:
Column A contains a series of dates in sequential order
Column B contains cash flows (+/-) associated with each date
Column C contains the account NAV at each date immediately following
the cash flow (this includes investment gains/losses between dates)
Right now, my dataset looks like this:
A B C
Date CF NAV
1/1/11 -100 100
1/31/11 0 102
2/15/11 -100 205
2/28/11 -100 304
What I'm ultimately trying to do is create a "rolling" IRR at each
date in column D using the XIRR function of Excel. The problem is
that XIRR only accepts contiguous (1 column or 1 row) cash flows and
dates. The terminal value (NAV) required for the calculation at each
date is located in column C, therefore I’ve decided to write my own
function in VBA.
So far I've tried using the Application.Union function in VBA with no
luck:
Application.Union("B1:B4","C4") hoping to return a single column range
like this:
-100
0
-100
-100
304
Basically, I’m appending the terminal value (column C) to the cash
flow series range (column B).
Any experience/advice on joining ranges into a single column
contiguous range would be much appreciated. Thank you.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
