Visitor

# Problem with VBA, inputs and loops not working

Nothing works with my code anymore. If i try to solve the first problem more promblems occour further down.  I whish to do three things: 1) determine the length of my input (number of cells) 2) A for-loop based on the length of input 3) find the average from the loop. Here is my code

``````Function TILLV(rng As Range) As Double

L = (UBound(rng, 1) - LBound(rng, 1) + 1)
Dim a() As Double
ReDim a(L)
Dim i As Integer

For i = 1 To L
If rng(i) < 0 And rng(i + 1) < 0 Then
a(i) = rng(i) / (rng(i + 1))
ElseIf rng(i) > 0 And rng(1 + 1) > 0 Then
a(i) = rng(i + 1) / rng(i)
ElseIf rng(i) < 0 And rng(i + 1) > 0 Then
a(i) = (rng(i + 1) - rng(i)) / (-rng(i))
Else
a(i) = 0
End If
Next i

average = ...
TILLV = average

End Function``````

Like I said, nothing works. I can't even figure out how to find the lenght of the input data (which is just a row of cells). When I try to use UBound i only get a message saying that it 'needs to be a matrix'.