OFFSET and SUMPRODUCT

[Floyd]

All:

Here is a complex function using OFFSET and SUMPRODUCT. I belive the
OFFSET commands are finally working, leaving the SUMPRODUCT.

I have compiled this function without error. However, when executing
the function I get #VALUE!.

Is there something wrong with the syntax for SUMPRODUCT?


Function FirstYearDepreciation(Current_Year As Double, Year_First As
Double, Fac_Depr As Integer)
Dim FirstRange As Range
Dim SecondRange As Range
Dim YearDelta As Double
Set FirstRange = Range("CI9")
Set SecondRange = Range("DO40")
YearDelta = Current_Year - Year_First


FirstYearDepreciation = Application.WorksheetFunction.SumProduct( _
FirstRange.Offset(-WorksheetFunction.Min(YearDelta, Fac_Depr), 0) _
.Resize(WorksheetFunction.Min(YearDelta + 1, Fac_Depr + 1),
1).Address, _
SecondRange.Offset(-WorksheetFunction.Min(YearDelta, Fac_Depr), 0)
_
.Resize(WorksheetFunction.Min(YearDelta + 1, Fac_Depr + 1),
1).Address)
End Function

 

[Floyd]

This might be easier to understand:

Function FirstYearDepreciation(Current_Year As Double, Year_First As
Double, Fac_Depr As Integer)
Dim FirstRange As Range
Dim SecondRange As Range
Dim YearDelta As Double
Set FirstRange = Range("CI9")
Set SecondRange = Range("DO40")
Dim SRng1Addr As String
Dim SRng2Addr As String
YearDelta = Current_Year - Year_First

SRng1Addr = FirstRange.Offset(-WorksheetFunction.Min(YearDelta,
Fac_Depr), 0).Resize(WorksheetFunction.Min(YearDelta + 1, Fac_Depr +
1), 1).Address(external:=True)
SRng2Addr = SecondRange.Offset(-WorksheetFunction.Min(YearDelta,
Fac_Depr), 0).Resize(WorksheetFunction.Min(YearDelta + 1, Fac_Depr +
1), 1).Address(external:=True)

FirstYearDepreciation = WorksheetFunction.SumProduct(SRng1Addr,
SRng2Addr)
End Function