dynamic range of formulas

is it possible to have a dynamic range that is filled with formulas and expands/contracts depending on another range?

here's what i mean:

ranges x (data) and y (formulas) will be the same size. range x gets pasted in first, so it determines the size of the ranges. i want range y to automatically assume the correct size and fill each cell with the correct formula.

right now, i clear the ranges, paste in range x, then put in the formulas. of course, this takes time and slows down the calculation.

obviously, i can simplify everything by putting the formulas into the worksheet and never dealing with it in code, but i don't do this for two reasons: 1) it's a waste of memory, and 2) the ranges have to be the same size.

ideally, i'd like the formulas to already be in the worksheet, and when the range expands/contracts, only those cells that are within the range have the formula. is this possible?

thanks.

for a literal intepretation of what you ask, there is no support for this.
x = "B9:B200"
Range(x).Offset(0,1).Formula="=If(" & Range(x)(1).Address(0,0) &
"=6,True,False)"

(as an example) is a possibility

--
Regards,
Tom Ogilvy

"dreamz" <[email protected]> wrote in
message news:[email protected]...
>
> is it possible to have a dynamic range that is filled with formulas and
> expands/contracts depending on another range?
>
> here's what i mean:
>
> ranges x (data) and y (formulas) will be the same size. range x gets
> pasted in first, so it determines the size of the ranges. i want range
> y to automatically assume the correct size and fill each cell with the
> correct formula.
>
> right now, i clear the ranges, paste in range x, then put in the
> formulas. of course, this takes time and slows down the calculation.
>
> obviously, i can simplify everything by putting the formulas into the
> worksheet and never dealing with it in code, but i don't do this for
> two reasons: 1) it's a waste of memory, and 2) the ranges have to be
> the same size.
>
> ideally, i'd like the formulas to already be in the worksheet, and when
> the range expands/contracts, only those cells that are within the range
> have the formula. is this possible?
>
> thanks.
>
>
> --
> dreamz
> ------------------------------------------------------------------------
> dreamz's Profile:
http://www.excelforum.com/member.php...o&userid=26462
> View this thread: http://www.excelforum.com/showthread...hreadid=506834
>

thanks for the reply, tom. i guess i'll still have to clear the cells, but your tip has been helpful. i never knew that you can do that with ranges. i learn something new every day!

cheers!