Insert IF-statements by Script, very slow.

[Parity]

I try to fill a column with individual IF-statements in a VB macro in a for-loop.
Something like:

Cells(row, 1).FormulaR1C1 = "=IF(bool,1,2)"
row = row + 1

It is much slower than inserting a constant statement.
I think the reason is, that Excel is going to calculate the result of the IF-statement at the same time the macro inserts the formulas.
Is there a way to prevent Excel from automatically updating the cells during the macro?

Thank you for your help so far.

Parity

[Norman Jones]

Hi Parity,

Instead of looping try something like:

Cells(x, 1).Resize(y).FormulaR1C1 = " = Your Formula"

Where x = the first formula row and y = the number of rows to receive the
formula.


---
Regards,
Norman



"Parity" <Parity.1xel6b_1130151907.8003@excelforum-nospam.com> wrote in
message news:Parity.1xel6b_1130151907.8003@excelforum-nospam.com...
>
> I try to fill a column with individual IF-statements in a VB macro in a
> for-loop.
> Something like:
>
> Cells(row, 1).FormulaR1C1 = "=IF(bool,1,2)"
> row = row + 1
>
> It is much slower than inserting a constant statement.
> I think the reason is, that Excel is going to calculate the result of
> the IF-statement at the same time the macro inserts the formulas.
> Is there a way to prevent Excel from automatically updating the cells
> during the macro?
>
> Thank you for your help so far.
>
> Parity
>
>
> --
> Parity
> ------------------------------------------------------------------------
> Parity's Profile:
> http://www.excelforum.com/member.php...o&userid=25023
> View this thread: http://www.excelforum.com/showthread...hreadid=478724
>

[Bob Phillips]

Set a couple of Excel things off, screenupdating and calculation, before the
code

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

and set them back afterwards


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Parity" <Parity.1xel6b_1130151907.8003@excelforum-nospam.com> wrote in
message news:Parity.1xel6b_1130151907.8003@excelforum-nospam.com...
>
> I try to fill a column with individual IF-statements in a VB macro in a
> for-loop.
> Something like:
>
> Cells(row, 1).FormulaR1C1 = "=IF(bool,1,2)"
> row = row + 1
>
> It is much slower than inserting a constant statement.
> I think the reason is, that Excel is going to calculate the result of
> the IF-statement at the same time the macro inserts the formulas.
> Is there a way to prevent Excel from automatically updating the cells
> during the macro?
>
> Thank you for your help so far.
>
> Parity
>
>
> --
> Parity
> ------------------------------------------------------------------------
> Parity's Profile:
http://www.excelforum.com/member.php...o&userid=25023
> View this thread: http://www.excelforum.com/showthread...hreadid=478724
>