Thanks for the response, but unfortunately this doesn't work with the
XIRR function as it only accepts 1-dimensional ranges. The resulting
‘urmg’ variable is a 2-dimensional range since the first range is in
column B and the second range is in column C.
The reulting urmg range basically looks like this:
100 0
0 0
-100 0
-100 304
I need it to be like:
-100
0
-100
-100
304
I need to append "C4" to the 1-dimensional range "B1:B4" (as if C4
were cut and pasted to B5 and the range was “B1:B5”) and have the
‘unrng’ be a 1-dimensional range only.
Do you know of a way to do this? Thanks.
On Sep 28, 12:17 pm, ashish koul <koul.ash...@gmail.com> wrote:
> Sub union_data()
> Dim s As Range, unrng As Range
> Set unrng = Union(Range("b1:b4"), Range("c4"))
> For Each s In unrng
> MsgBox s.Value
> Next
> End Sub
>
>
>
>
>
> On Wed, Sep 28, 2011 at 9:04 PM, excelCPA <jon.wester...@gmail.com> wrote:
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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
>
> --
> *Regards*
> * *
> *Ashish Koul*
> *http://www.excelvbamacros.com/*
>
> P Before printing, think about the environment.- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
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
