excel - 提高循环速度

Question

我在不同的论坛上搜索了很多信息，但是因为我是 VBA 的初学者，所以我没有找到我要找的东西。

我构建了一个包含很多“For”循环的宏，但是宏越复杂，它需要运行的时间就越多。而且，我很确定我可以改进我的代码。

以下是我使用“通用”运行的两个宏（所有 Screeupdating=false 等...）：

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

如果您能帮助我改进这些宏，我将不胜感激！！！（我尝试使用变体但我没有成功..）

谢谢 ！问候，

标签： excelvba