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 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
>
--
*Regards*
* *
*Ashish Koul*
*http://www.excelvbamacros.com/*
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
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
