Is there a way to speed up this code?

[Captain Smith]

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

 

[Weaver]

try

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

 

[Fazza]

Should L1:L3000 change to L1:T3000 ?

 

[NateO]

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

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!

 

[Weaver]

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

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

 

[Captain Smith]

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

 

[NateO]

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

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

To this:

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

Would be potentially faster, to recalc before you redraw the screen.