Code take too long to run

[mdgabe]

I have this block of code that takes more than five seconds to run, appreciate any insights in making this work faster

With Worksheets("Country-Product Report")
        If Worksheets("raw2").Range("H" & chrw) <> 0 Then
            shr = Worksheets("raw2").Range("E" & chrw) / Worksheets("raw2").Range("H" & chrw)
        End If
        Range("E" & rw).Value = Worksheets("raw2").Range("E" & chrw).Value       'Product
        Range("F" & rw).Value = Worksheets("raw2").Range("H" & chrw).Value       'Market
        Range("G" & rw).Value = shr                                             'Market Share
        Range("H" & rw).Value = Worksheets("raw2").Range("Q" & chrw).Value       'EI
        Range("I" & rw).Value = Worksheets("raw2").Range("T" & chrw).Value       'Growth
        Range("J" & rw).Value = Worksheets("raw2").Range("W" & chrw).Value       'Mkt Growth
    End With
    'Put in YTD values
    With Worksheets("Country-Product Report")
        If Worksheets("raw2").Range("J" & chrw) <> 0 Then
            shr = Worksheets("raw2").Range("G" & chrw) / Worksheets("raw2").Range("J" & chrw)
        End If
        Range("K" & rw).Value = Worksheets("raw2").Range("G" & chrw).Value       'Product
        Range("L" & rw).Value = Worksheets("raw2").Range("J" & chrw).Value       'Market
        Range("M" & rw).Value = shr                                             'Market Share
        Range("N" & rw).Value = Worksheets("raw2").Range("S" & chrw).Value       'EI
        Range("O" & rw).Value = Worksheets("raw2").Range("V" & chrw).Value       'Growth
        Range("P" & rw).Value = Worksheets("raw2").Range("Y" & chrw).Value       'Mkt Growth
    End With
    'Put in MAT values
    With Worksheets("Country-Product Report")
        If Worksheets("raw2").Range("I" & chrw) <> 0 Then
            shr = Worksheets("raw2").Range("F" & chrw) / Worksheets("raw2").Range("I" & chrw)
        End If
        Range("Q" & rw).Value = Worksheets("raw2").Range("F" & chrw).Value       'Product
        Range("R" & rw).Value = Worksheets("raw2").Range("I" & chrw).Value       'Market
        Range("S" & rw).Value = shr                                             'Market Share
        Range("T" & rw).Value = Worksheets("raw2").Range("R" & chrw).Value       'EI
        Range("U" & rw).Value = Worksheets("raw2").Range("U" & chrw).Value       'Growth
        Range("V" & rw).Value = Worksheets("raw2").Range("X" & chrw).Value       'Mkt Growth
    End With

 

[CautionMP]

mdgabe,
If [tt]Worksheets("raw2")[/tt] has calculations based on the values your placing with the code, try turning the calculations off while you move the values, then turn calculations back on.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[macropod]

Hi mdgabe,

You could delete the intermediate
End With
With Worksheets("Country-Product Report")
statements, as they don't really contribute anything, and you could make the IF tests in-line, but neither of these is likely to make much difference to the execution time.

CautionMP has suggested changing the calculation behaviour "If Worksheets("raw2") has calculations based on the values your placing with the code", but I think it far more likely that the code's performance would be hampered by any calculations resulting from changes to the cells you're updating on the "Country-Product Report" worksheet.

As a rule, it's better to toggle both screen updating and automatic calculations for maximum speed. You could do by having any routines you want to speed up calling the following subs at the start and end, respectively.

Private Sub MacroEntry()
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Cheers

[MS MVP - Word]

 

[mdgabe]

Thanks for the replies, screen updating I have it already set to false prior to this step.

I was thinking that the copy and paste cell by cell was maybe slowing things down, but my skills in VBA are not advanced enough to do anything else.

 

[macropod]

Hi mdgabe,

Your use of range objects to replicate the values is already quite efficient - much more so than using selection and/or copy & paste functions. Toggling auto calc is the only other change that's likely to make much difference to how fast the code runs; 5+ seconds for what it's doing does seem excessive.

Cheers

[MS MVP - Word]

 

[combo]

If you have a loop and chrw is the row counter, you can consider copy ranges' values into two 2D variant arrays, proceed with those arrays and copy back te results into worksheets. Depending on the worksheet structure, it may be necessary to separate values and formulas.
The idea:

Dim vArray() As Variant, rRange As Range
Set rRange = Worksheets(1).Range("A1:B2")
ReDim vArray(1 To 2, 1 To 2)
vArray = rRange ' assign worksheet's values to array
vArray(1, 1) = 2 * vArray(1, 1) ' perform calculations
rRange = vArray ' return results to worksheet

combo