Copy down a range of formulae

[Guest]

Is there any procedure to copy down a range of formulae (say A6:J6) based on
a cell value (b2)? I have formulae on a6:j6. I would like to copy down them
upto the value that is entered in b2.
Thank you all
Sridhar

 

[Guest]

without VB, you could number a predetermined set of rows. Then put your
formula into an 'if' statement that says if(currentrow<b2,formula,"")to keep
the cell blank. if you had the formulas already copied and in place, you
would see only blanks for the rows past the number you chose in b2.

 

[Sandy Mann]

If you mean for example with 6 in B1, for the 5 rows below A6:J6 to show a
result of a formula then enter in A7:J7 a formula like:

=IF(ROW()-6<$B$1,<Your formula>,"")

and copy down as far as you will ever need. The formulas will only show
down as far ar the number of rows sprecified in B1.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thank you Sandy. The formula works but not my problem. I have to copy down
the formulae upto J1000. As these are array formulae it is taking some time
to finish the calculation. What i want is a procedure that removes the
formulae first from A7:J (previous copy range), and copies the formulae of
A6:j6 down the value in B1. Any suggestions pls.
Thank you once again Sandy for your formula.
Regards
Sridhar

 

[Sandy Mann]

Copying formulas down by VBA will be slow and then the array formula will
still have to calculate so what you are asking for will be slower, not
faster, except for small numbers of rows.

As an experiment I created a formula in A1:

=IF($K$1=1,"",SUMPRODUCT((M1:Z10000)*(N1:N10000)))

and copied it across to J1 and then down to Row 1037 - some 10,370 formulas

Deleting the 1 from K1 caused the SUMPRODUCT() part to calculate and it took
about 10 minutes to calculate.

Entering the 1 back into K1 whereupon only the $K$1=1,"" part calculated
caused an almost instantaneous recalculation, certainly much too fast for me
to be manually time it.

It follows therefore that the fastest way will be :

=IF(ROW()-6>$B$1,"",<Your formula>)

That way the only array formulas that will be calculate are those in the
rows up to the value in B1 and they would have to have been calculated your
way anyway.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thank you Sandy for your explanation. After your suggestion i changed my
data and reduced the formulae. Thanks alot
Sridhar

 

[Sandy Mann]

You're very welcome, Gladthat you got it worked out.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk