Insert IF-statements by Script, very slow.
 

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

Insert IF-statements by Script, very slow.
 

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

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

Insert IF-statements by Script, very slow.
 

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

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