Help required with UDF
 

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.

Help required with UDF
 

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.

Help required with UDF
 

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

Set Temp_array = range(....

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

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