Challenge Converting Non-Contiguous References
 

Ive created the following function to convert non-contiguous references into
a contiguous reference:

Function Contiguous(ParamArray ArgList() As Variant)
Dim x As Integer
Dim Argument
Dim Cell As Range
Dim ReturnArray()
For Each Argument In ArgList
If TypeName(Argument) = "Range" Then
ReDim Preserve ReturnArray(0 To x + Argument.Count)
For Each Cell In Argument
ReturnArray(x) = Cell
x = x + 1
Next Cell
Else
ReDim Preserve ReturnArray(0 To x)
ReturnArray(x) = Argument
x = x + 1
End If
Next Argument
Contiguous = ReturnArray
End Function

This function is very handy in conjunction with a number of formulas, such
as IRR, NPV, etc. [ e.g. =IRR(contiguous(-A1,C1:C10,E2+E10)) or
=NPV(10%,contiguous(A1,C1:C10,E10)) ]

However, the function does not work with selected functions such as COUNTIF
or SUMIF [ e.g. =SUMIF(contiguous(A1,C1:C10,E10),€�1000€�) ]. The Contiguous
function does not work with selected user-defined formulas as well such as
the ReverseIRR function below [ e.g. =ReverseIRR(contiguous(-A1,C1:C10,E2)) ]:

Function ReverseIRR(ByVal InterestRate As Double, CashFlows)
Dim x As Integer
For x = 1 To CashFlows.Count
ReverseIRR = ReverseIRR - CashFlows(x) * (1 + InterestRate) ^
(CashFlows.Count - x)
Next
End Function

Interesting enough, the ReverseIRR function works if I make as slight
modification as follows [e.g. =ReverseIRRv2(contiguous(-A1,C1:C10,E2)) ]:

Function ReverseIRRv2(ByVal InterestRate As Double, CashFlows)
Dim x As Integer
Dim MaxRecords As Integer
If TypeName(CashFlows) = "Range" Then
MaxRecords = CashFlows.Count
Else
MaxRecords = UBound(CashFlows) - 1
End If
For x = 1 To MaxRecords
ReverseIRRv2 = ReverseIRRv2 - CashFlows(x) * (1 + InterestRate) ^
(MaxRecords - x)
Next
End Function

I presume (perhaps erroneously) that the problem results from the fact that
the Contiguous function returns an array not a range. If this is in fact the
problem, how do I get the above function to return a range? Any thoughts?

Thanks a million!

Challenge Converting Non-Contiguous References
 

Late on a Friday....
cheat
create a new sheet (visible = false) and copy the array onto the new sheet
and then get a range reference- do stuff - then delete the new sheet

"Brian" wrote:

Ive created the following function to convert non-contiguous references into
a contiguous reference:

Function Contiguous(ParamArray ArgList() As Variant)
Dim x As Integer
Dim Argument
Dim Cell As Range
Dim ReturnArray()
For Each Argument In ArgList
If TypeName(Argument) = "Range" Then
ReDim Preserve ReturnArray(0 To x + Argument.Count)
For Each Cell In Argument
ReturnArray(x) = Cell
x = x + 1
Next Cell
Else
ReDim Preserve ReturnArray(0 To x)
ReturnArray(x) = Argument
x = x + 1
End If
Next Argument
Contiguous = ReturnArray
End Function

This function is very handy in conjunction with a number of formulas, such
as IRR, NPV, etc. [ e.g. =IRR(contiguous(-A1,C1:C10,E2+E10)) or
=NPV(10%,contiguous(A1,C1:C10,E10)) ]

However, the function does not work with selected functions such as COUNTIF
or SUMIF [ e.g. =SUMIF(contiguous(A1,C1:C10,E10),€�1000€�) ]. The Contiguous
function does not work with selected user-defined formulas as well such as
the ReverseIRR function below [ e.g. =ReverseIRR(contiguous(-A1,C1:C10,E2)) ]:

Function ReverseIRR(ByVal InterestRate As Double, CashFlows)
Dim x As Integer
For x = 1 To CashFlows.Count
ReverseIRR = ReverseIRR - CashFlows(x) * (1 + InterestRate) ^
(CashFlows.Count - x)
Next
End Function

Interesting enough, the ReverseIRR function works if I make as slight
modification as follows [e.g. =ReverseIRRv2(contiguous(-A1,C1:C10,E2)) ]:

Function ReverseIRRv2(ByVal InterestRate As Double, CashFlows)
Dim x As Integer
Dim MaxRecords As Integer
If TypeName(CashFlows) = "Range" Then
MaxRecords = CashFlows.Count
Else
MaxRecords = UBound(CashFlows) - 1
End If
For x = 1 To MaxRecords
ReverseIRRv2 = ReverseIRRv2 - CashFlows(x) * (1 + InterestRate) ^
(MaxRecords - x)
Next
End Function

I presume (perhaps erroneously) that the problem results from the fact that
the Contiguous function returns an array not a range. If this is in fact the
problem, how do I get the above function to return a range? Any thoughts?

Thanks a million!