Help required with UDF
Oops. Didn't see you found the answer until after I posted.
Geoff wrote:
I've managed to get this running now, addressed the omission of the 'Set'
statement and modified the function to use number of columns rather than
number of years. Cheers :)
--
There are 10 types of people in the world - those who understand binary and
those who don't.
"Geoff" wrote:
I have written a UDF as below:
Function Sum_PayYr(YearEnd As Range)
'Sums the numbers from all quarters belonging to a given year
Dim sum_Value As Double
Dim temp_Array As Range
Dim num_Cols As Integer
Dim i As Integer
Dim k As Range
num_Cols = Year(YearEnd.Value) - 1975
sum_Value = 0
For i = 0 To num_Cols
temp_Array = Range(YearEnd.Offset(1 + 4 * i, -i), YearEnd.Offset(4 + 4 *
i, -i))
For Each k In temp_Array
sum_Value = sum_Value + k.Value
Next k
Next i
Sum_PayYr = sum_Value
End Function
The idea is that this will grab 4 cells from the column directly underneath
the starting cell (containing a date value), add their values to the return
value, then offset by -1 columns and 4 rows and repeat the process, looping
through num_Cols times. Everything initialises ok until temp_Array, at which
point execution appears to stop without any exception notice for some reason.
Does anyone have any idea why this is happening?
--
There are 10 types of people in the world - those who understand binary and
those who don't.
--
Dave Peterson
|