Is there a way to speed up this code?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Is there a way to speed up this code?

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    8,413
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    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,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    324
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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