Help required with UDF

[Guest]

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?

 

[Guest]

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

 

[Dave Peterson]

Since temp_array is a range, you'll need to use "Set"

Set Temp_array = range(....

 

[Dave Peterson]

Oops. Didn't see you found the answer until after I posted.