Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Script to insert Row | Excel Discussion (Misc queries) | |||
Need VBA script to auto-insert value upon row insert | Excel Worksheet Functions | |||
Excel vba script extremely slow | Excel Programming | |||
Script to insert the next Friday | Excel Programming | |||
Script to insert the next Friday | Excel Programming |