Limit function's arguement to a cell value on the same row

[Bill Schoenhut]

Here's the situation: I have created a function in VBA
that adds 5 to the value it is given in its arguement
called AddFive. I have a range called Dim1 of 5 different
cells all under each other. In the next column over I have
typed my formula =AddFive(Dim1). Unfortunately the
function will not work since Dim1 returns an array of
values instead of the value located on the same row. Is
there a way around this problem?

Thanks,
Bill

 

[Dick Kusleika]

Bill

Try this

Function AddFive(Rng As Variant) As Double

If IsArray(Rng) Then
AddFive = Rng(Application.Caller.Row - _
Rng.Row + 1, 1) + 5
ElseIf TypeName(Rng) = "Range" Then
AddFive = Rng.Value + 5
Else
AddFive = Rng + 5
End If

End Function