Results 1 to 7 of 7

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. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    323

    Default 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. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,170

    Default 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])"
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    7,803

    Default Re: Is there a way to speed up this code?

    Should L1:L3000 change to L1:T3000 ?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default 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. #5
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,170

    Default 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])"
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  6. #6
    Board Regular
    Join Date
    Feb 2003
    Posts
    323

    Default Re: Is there a way to speed up this code?

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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default 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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com