# Is there a way to speed up this code?

This is a discussion on Is there a way to speed up this code? within the Excel Questions forums, part of the Question Forums category; Is there a better way to write this code so it goes faster? Right now it takes approx 2 minutes ...

1. ## Is there a way to speed up this code?

Is there a better way to write this code so it goes faster? Right now it takes approx 2 minutes to complete. Thank you.

Sub Macro5()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("L1:L3000")
cell.FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 1).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 2).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 3).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 4).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 5).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 6).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 7).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
cell.Offset(0, 8).FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"
Next cell
Application.ScreenUpdating = True
MsgBox ("done")
End Sub

2. ## Re: Is there a way to speed up this code?

try

Code:
`Range("L1:L3000").FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"`

3. ## Re: Is there a way to speed up this code?

Should L1:L3000 change to L1:T3000 ?

4. ## Re: Is there a way to speed up this code?

This runs in less than a second on my machine (XL 2007):

Code:
```Sub foo()
Dim clcMode As Long
With Application
.ScreenUpdating = False
Let clcMode = .Calculation
.Calculation = xlCalculationManual
End With

Let Range("L1:T3000").FormulaR1C1 = _
"=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"

With Application
.ScreenUpdating = True
.Calculation = clcMode
End With

MsgBox ("done")
End Sub```
You're probably throttling the Calc Engine...

Let it happen, Captain!

5. ## Re: Is there a way to speed up this code?

Oops of course - forgot to change it after I copied & pasted from the original post. Cheers

Code:
`Range("L1:T3000").FormulaR1C1 = "=IF(Consolidation!R[7]C[-3]="""","""",Consolidation!R[7]C[-3])"`

6. ## Re: Is there a way to speed up this code?

WOW, thanks both of you. This is super speedy now.

7. ## Re: Is there a way to speed up this code?

You're welcome. It also occurs to me that changing this:

Code:
```With Application
.ScreenUpdating = True
.Calculation = clcMode
End With```
To this:

Code:
```With Application
.Calculation = clcMode
.ScreenUpdating = True
End With```
Would be potentially faster, to recalc before you redraw the screen.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•