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