excel - Custom Function Entering the Result Array into a Different Cell -

i have created own function determine count values in between given values in increments of 30 seen here

function inbetween(first integer, last integer) dim long, f string, a() string f = first = first + 30 last step 30 f = f & "|" & next inbetween = f end function 

when use function, have returning result array in cell formula entered in format of "1|2|3|4". there way can array populate cell below 1 containing formula?

note: don't want formula in cell need refer cell in future equation use result , not equation.

this surprisingly difficult. @ first tried calling sub function affect cell below using application.caller returned #value error. seems udf can't run affects worksheet.

eventually came this:

create worksheet change event pasting worksheet object in vb:

private sub worksheet_selectionchange(byval target range) on error resume next if left(target.offset(-1, 0).formula, 10) = "=inbetween" call dox(target.offset(-1, 0), inbetween(10, 60)) on error goto 0 end sub 

then paste module

sub dox(r range, val string)  sheets(r.parent.name).cells(r.row, r.column) = "" sheets(r.parent.name).cells(r.row + 1, r.column) = val  end sub 

then use function normal, remember hit return after enter active cell cell below entered formula.