dynamic range of formulas
 

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

here's what i mean:

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

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

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

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

thanks

--
dream
-----------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...fo&userid=2646
View this thread: http://www.excelforum.com/showthread.php?threadid=50683

dynamic range of formulas
 

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" wrote in
message ...

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

dynamic range of formulas
 

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 wit
ranges. i learn something new every day!

cheers

--
dream
-----------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...fo&userid=2646
View this thread: http://www.excelforum.com/showthread.php?threadid=50683