Hi,

I got stuck with the following problem:

I need a function that can return a variable amount of numbers down the
column. First, I tried to change the active cell, but this didn't work.
Currently, I'm using an array as output. But I'm not happy with this
solution either because one needs to select the right number of cells
in advance.

Any ideas are welcome.

Hi Renderman,

Can you not use Selection.Count to redim your array and as an upper
limit to any loops your function might use?

While you're at it you may want to check that Areas.Count is only 1
unless your function can cope with multiple areas.

Without seeing the function code I can't tell if this will be of any
help to you.

NickH

Thanks for your response.
I'm not sure that I understand what you are suggesting.

I added an example code. The function takes the range and returns only
the negative numbers. As it is, the user has to select enough cells and
use it as an array function (Ctrl+Shift+Enter).

Function testit(inputRange As Range)

Dim output() As Double
Dim i As Integer

i = 0

For Each c In inputRange.Cells

If (c.Value < 0) Then
ReDim Preserve output(i)
output(UBound(output)) = c.Value
i = i + 1
End If

Next c

testit = output

End Function