excel - 循环优化: merge two loops into one

Question

我写了下面的两个循环:

Dim intLstRowA As Integer
Dim intLstRowB As Integer

intLstRowA = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
intLstRowB = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To intLstRowA
        Sheets(1).Cells(i, 22).Value2 = Sheets(1).Cells(i, 4).Value2 * Sheets(1).Cells(i, 5).Value2
        Sheets(1).Cells(i, 23).Value2 = Sheets(1).Cells(i, 4).Value2 * Sheets(1).Cells(i, 6).Value2
        Sheets(1).Cells(i, 24).Value2 = Sheets(1).Cells(i, 4).Value2 * Sheets(1).Cells(i, 9).Value2
        Sheets(1).Cells(i, 25).Value2 = Sheets(1).Cells(i, 4).Value2 * Sheets(1).Cells(i, 19).Value2
        Sheets(1).Cells(i, 26).Value2 = Sheets(1).Cells(i, 4).Value2 * Sheets(1).Cells(i, 20).Value2
Next i
For i = 2 To intLstRowB
        Sheets(2).Cells(i, 22).Value2 = Sheets(2).Cells(i, 4).Value2 * Sheets(2).Cells(i, 5).Value2
        Sheets(2).Cells(i, 23).Value2 = Sheets(2).Cells(i, 4).Value2 * Sheets(2).Cells(i, 6).Value2
        Sheets(2).Cells(i, 24).Value2 = Sheets(2).Cells(i, 4).Value2 * Sheets(2).Cells(i, 9).Value2
        Sheets(2).Cells(i, 25).Value2 = Sheets(2).Cells(i, 4).Value2 * Sheets(2).Cells(i, 19).Value2
        Sheets(2).Cells(i, 26).Value2 = Sheets(2).Cells(i, 4).Value2 * Sheets(2).Cells(i, 20).Value2
Next i

有两个循环，因为 intLstRowA 与 intLstRowB 不同(通常差异是从 20 到 50)，否则我会添加一个“j”值(从 1 到 2)来在 Sheets(1) 和 Sheets( 2).

有什么想法吗？

Answer 1

这已经是我能得到的最紧的了。

Dim i As Long, v As Long, s As Long, vCOLs As Variant

vCOLs = Array(Array(22, 23, 24, 25, 26), Array(5, 6, 9, 19, 20))

For s = 1 To 2
    With Sheets(s)
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            For v = LBound(vCOLs(1)) To UBound(vCOLs(1))
                .Cells(i, vCOLs(0)(v)) = .Cells(i, 4).Value2 * .Cells(i, vCOLs(1)(v)).Value2
            Next v
        Next i
    End With
Next s

这是通过将二维数组的两个等级都投入工作来为计算的源和目标提供列索引号来实现的。

将进行编译，但不会针对示例数据进行现场测试。