I'm trying to write a function that will populate the cell that called
the function and x number of cells below it. For example

in cell A1, i make a call to my function "=GetList()"

GetList should put the numbers 1 - 10 in cells a1 - a10

its driving me crazy

Sorry to let you know that you can not use function to change cells'
contents. You need a sub rather than function.

Here is an indirect way without coding:

Assume you will put a number in A1 and the max X you willn enter is 20
in A2 type formula
=IF(ISNUMBER(A1), IF(A1-10,A1-1,""),"")
copy and paste formula to A3:A20
So when you type 10 in A1, A1 to A10 will be filled with 10 to 1 (in reverse
order) and A11 to A20 will be blank.

Hope this helps.


"4eyed" wrote in message
ups.com...
I'm trying to write a function that will populate the cell that called
the function and x number of cells below it. For example

in cell A1, i make a call to my function "=GetList()"

GetList should put the numbers 1 - 10 in cells a1 - a10

its driving me crazy

4eyed wrote:
I'm trying to write a function that will populate the cell that called
the function and x number of cells below it. For example

in cell A1, i make a call to my function "=GetList()"

GetList should put the numbers 1 - 10 in cells a1 - a10

its driving me crazy


David Chen's response should have been that with a function called from
a worksheet you cannot change the contents of cells *other than those
into which the function is entered*.

Array enter into A1:A10 =GetList()

Function GetList()
Dim arr
N = 10
ReDim arr(1 To N, 1 To 1)
For i = 1 To N
arr(i, 1) = i
Next
GetList = arr
End Function

Alan Beban