Increase Speed of a Loop

Question

I've searched a lot of information on different forums, but because I'm a beginner in VBA, I didn't find what I am looking for.

I built a macro with a lot of "For" loops in it, but the more the macro become complex the more time it needs to run. And, I am pretty sure that I can improve my code.

Here are two on my macros that I am running with a "general" one (with all Screeupdating=false, etc...) :

Sub stock()

Dim x As Long, i As Long, y As Long

x = ThisWorkbook.Worksheets("STOCK L1").UsedRange.Rows.Count: y = ActiveWorkbook.Worksheets("Production Process").UsedRange.Rows.Count
For i = 0 To ((y - 178) / 26)
    If ActiveWorkbook.Worksheets("Production Process").Cells(171 + (26 * i), 3) <> 0 Then
    ActiveWorkbook.Worksheets("Production Process").Cells(171 + (26 * i), 3).Copy
    ThisWorkbook.Worksheets("STOCK L1").Cells(171 + (26 * i) + x, 2).PasteSpecial Paste:=xlPasteValues
    ThisWorkbook.Worksheets("STOCK L1").Cells(171 + (26 * i) + x, 3).PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.Worksheets("Production Process").Cells(178 + (26 * i), 1).EntireRow.Copy
    ThisWorkbook.Worksheets("STOCK L1").Cells(178 + (26 * i) + x, 1).PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.Worksheets("Production Process").Cells(181 + (26 * i), 1).EntireRow.Copy
    ThisWorkbook.Worksheets("STOCK L1").Cells(181 + (26 * i) + x, 1).PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.Worksheets("Production Process").Cells(187 + (26 * i), 1).EntireRow.Copy
    ThisWorkbook.Worksheets("STOCK L1").Cells(187 + (26 * i) + x, 1).PasteSpecial Paste:=xlPasteValues
    End If
Next

End Sub

Sub macro2()

Dim x As Long, y As Long, z As Long, i As Long, j As Long, k As Long

x = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count: y = ThisWorkbook.Worksheets(2).UsedRange.Rows.Count: z = ThisWorkbook.Worksheets(3).UsedRange.Rows.Count

For i = 2 To x
    For j = 4 To z
        If ThisWorkbook.Worksheets(1).Cells(i, 1) <> "" And ThisWorkbook.Worksheets(1).Cells(i, 1) = ThisWorkbook.Worksheets(3).Cells(j, 23) Then
            For k = 4 To y
                If ThisWorkbook.Worksheets(3).Cells(j, 1) = ThisWorkbook.Worksheets(2).Cells(k, 1) Then
                    ThisWorkbook.Worksheets(1).Cells(i, 2) = ThisWorkbook.Worksheets(1).Cells(i, 2) + ThisWorkbook.Worksheets(2).Cells(k, 3)
                    ThisWorkbook.Worksheets(1).Cells(i, 3) = ThisWorkbook.Worksheets(1).Cells(i, 3) + ThisWorkbook.Worksheets(2).Cells(k, 4)
                    ThisWorkbook.Worksheets(1).Cells(i, 4) = ThisWorkbook.Worksheets(1).Cells(i, 4) + ThisWorkbook.Worksheets(2).Cells(k, 5)
                    ThisWorkbook.Worksheets(1).Cells(i, 5) = ThisWorkbook.Worksheets(1).Cells(i, 5) + ThisWorkbook.Worksheets(2).Cells(k, 6)
                    ThisWorkbook.Worksheets(1).Cells(i, 6) = ThisWorkbook.Worksheets(1).Cells(i, 6) + ThisWorkbook.Worksheets(2).Cells(k, 7)
                    ThisWorkbook.Worksheets(1).Cells(i, 7) = ThisWorkbook.Worksheets(1).Cells(i, 7) + ThisWorkbook.Worksheets(2).Cells(k, 8)
                    ThisWorkbook.Worksheets(1).Cells(i, 8) = ThisWorkbook.Worksheets(1).Cells(i, 8) + ThisWorkbook.Worksheets(2).Cells(k, 9)
                    ThisWorkbook.Worksheets(1).Cells(i, 9) = ThisWorkbook.Worksheets(1).Cells(i, 9) + ThisWorkbook.Worksheets(2).Cells(k, 10)
                    ThisWorkbook.Worksheets(1).Cells(i, 10) = ThisWorkbook.Worksheets(1).Cells(i, 10) + ThisWorkbook.Worksheets(2).Cells(k, 11)
                    ThisWorkbook.Worksheets(1).Cells(i, 11) = ThisWorkbook.Worksheets(1).Cells(i, 11) + ThisWorkbook.Worksheets(2).Cells(k, 12)
                    ThisWorkbook.Worksheets(1).Cells(i, 12) = ThisWorkbook.Worksheets(1).Cells(i, 12) + ThisWorkbook.Worksheets(2).Cells(k, 13)
                    ThisWorkbook.Worksheets(1).Cells(i, 13) = ThisWorkbook.Worksheets(1).Cells(i, 13) + ThisWorkbook.Worksheets(2).Cells(k, 14)
                    ThisWorkbook.Worksheets(1).Cells(i, 14) = ThisWorkbook.Worksheets(1).Cells(i, 14) + ThisWorkbook.Worksheets(2).Cells(k, 15)
                    ThisWorkbook.Worksheets(1).Cells(i, 15) = ThisWorkbook.Worksheets(1).Cells(i, 15) + ThisWorkbook.Worksheets(2).Cells(k, 16)
                    ThisWorkbook.Worksheets(1).Cells(i, 16) = ThisWorkbook.Worksheets(1).Cells(i, 16) + ThisWorkbook.Worksheets(2).Cells(k, 17)
                    ThisWorkbook.Worksheets(1).Cells(i, 17) = ThisWorkbook.Worksheets(1).Cells(i, 17) + ThisWorkbook.Worksheets(2).Cells(k, 18)
                    ThisWorkbook.Worksheets(1).Cells(i, 18) = ThisWorkbook.Worksheets(1).Cells(i, 18) + ThisWorkbook.Worksheets(2).Cells(k, 19)
                    ThisWorkbook.Worksheets(1).Cells(i, 19) = ThisWorkbook.Worksheets(1).Cells(i, 19) + ThisWorkbook.Worksheets(2).Cells(k, 20)
                    ThisWorkbook.Worksheets(1).Cells(i, 20) = ThisWorkbook.Worksheets(1).Cells(i, 20) + ThisWorkbook.Worksheets(2).Cells(k, 21)
                    ThisWorkbook.Worksheets(1).Cells(i, 21) = ThisWorkbook.Worksheets(1).Cells(i, 21) + ThisWorkbook.Worksheets(2).Cells(k, 22)
                End If
            Next
        End If
    Next
Next

End Sub

If you are able helping me to improve these macros, I would be very grateful !!! (I tried to use variant but I didn't succeed..)

Thanks ! Regards,

@SolarMike Would need some descriptions and context, but it could be smacked into submission I guess. — Mast, Jul 16 '20 at 17:38
First, use some worksheet variables (`ws1,ws2,ws3` for example) so you/we can actually see what 's going on without slogging through reams of `ThisWorkbook.Worksheets(x)`. Then look at using variant arrays -pick the data off the sheet, work with it, then put it back on the sheet. That's *much* faster than going cell-by cell. Also, using things like `Application.Match` (against the data on the worksheet) is much faster than looping cell by cell. — Tim Williams, Jul 16 '20 at 17:42
...and that inner block of code in `macro2` can be a loop from 2 to 21... — Tim Williams, Jul 16 '20 at 17:58
I didn't put a loop from 2 to 21 to avoid wasting time again — Psilo, Jul 16 '20 at 19:11
I heard about this : "Then look at using variant arrays -pick the data off the sheet, work with it, then put it back on the sheet." But I actually don't know how to manage it ! :/ — Psilo, Jul 16 '20 at 19:12
Have a look at this for using different sheet names: https://stackoverflow.com/q/50776026/4961700 — Solar Mike, Jul 16 '20 at 20:35
I think this is already what I am doing...In my opinion, the only way improving the speed is to use Variant array, but I don't know how to do it...is someone can do it on my `shift` macro ? — Psilo, Jul 16 '20 at 22:36
dim variables to store sheet objects so instead of repeatedly doing this: ActiveWorkbook.Worksheets("Production Process").cells... declare a local variable dim oProdSheet as worksheet set oProdSeet = ActiveWorkbook.Worksheets("Production Process") then use oProdSheet inside your loops — Joe Bourne, Jul 17 '20 at 00:10

Increase Speed of a Loop

0 Answers0