excel - 再次与嵌套循环VBA混淆

再一次，我遇到了一些本来应该很基本的问题。我知道有一个更快的解决方案可以解决这个问题，但是我似乎无法正确地解决问题。

从本质上讲，我想缩短这段代码，因为我要处理下一个更大的表和数组，而下面的当前解决方案将需要很多行。

源表有10列和4行。.我需要从源表中读取数据，并将其分配给目标表“ d105WeeklyTable”的一行

任何帮助将不胜感激。

With ActiveSheet.ListObjects("d105WeeklyTable")
    For i = 1 To .ListRows.Count
        If .DataBodyRange(i, 2).Value = wkComm Then

                .DataBodyRange(i, 132).Value = d105TicksheetNightsFigures(1, 2)
                .DataBodyRange(i, 133).Value = d105TicksheetNightsFigures(1, 3)
                .DataBodyRange(i, 134).Value = d105TicksheetNightsFigures(1, 4)
                .DataBodyRange(i, 135).Value = d105TicksheetNightsFigures(1, 5)
                .DataBodyRange(i, 136).Value = d105TicksheetNightsFigures(1, 6)
                .DataBodyRange(i, 137).Value = d105TicksheetNightsFigures(1, 7)
                .DataBodyRange(i, 138).Value = d105TicksheetNightsFigures(1, 8)
                .DataBodyRange(i, 139).Value = d105TicksheetNightsFigures(1, 9)
                .DataBodyRange(i, 140).Value = d105TicksheetNightsFigures(1, 10)

                .DataBodyRange(i, 141).Value = d105TicksheetNightsFigures(2, 2)
                .DataBodyRange(i, 142).Value = d105TicksheetNightsFigures(2, 3)
                .DataBodyRange(i, 143).Value = d105TicksheetNightsFigures(2, 4)
                .DataBodyRange(i, 144).Value = d105TicksheetNightsFigures(2, 5)
                .DataBodyRange(i, 145).Value = d105TicksheetNightsFigures(2, 6)
                .DataBodyRange(i, 146).Value = d105TicksheetNightsFigures(2, 7)
                .DataBodyRange(i, 147).Value = d105TicksheetNightsFigures(2, 8)
                .DataBodyRange(i, 148).Value = d105TicksheetNightsFigures(2, 9)
                .DataBodyRange(i, 149).Value = d105TicksheetNightsFigures(2, 10)

                .DataBodyRange(i, 150).Value = d105TicksheetNightsFigures(3, 2)
                .DataBodyRange(i, 151).Value = d105TicksheetNightsFigures(3, 3)
                .DataBodyRange(i, 152).Value = d105TicksheetNightsFigures(3, 4)
                .DataBodyRange(i, 153).Value = d105TicksheetNightsFigures(3, 5)
                .DataBodyRange(i, 154).Value = d105TicksheetNightsFigures(3, 6)
                .DataBodyRange(i, 155).Value = d105TicksheetNightsFigures(3, 7)
                .DataBodyRange(i, 156).Value = d105TicksheetNightsFigures(3, 8)
                .DataBodyRange(i, 157).Value = d105TicksheetNightsFigures(3, 9)
                .DataBodyRange(i, 158).Value = d105TicksheetNightsFigures(3, 10)

                .DataBodyRange(i, 159).Value = d105TicksheetNightsFigures(4, 2)
                .DataBodyRange(i, 160).Value = d105TicksheetNightsFigures(4, 3)
                .DataBodyRange(i, 161).Value = d105TicksheetNightsFigures(4, 4)
                .DataBodyRange(i, 162).Value = d105TicksheetNightsFigures(4, 5)
                .DataBodyRange(i, 163).Value = d105TicksheetNightsFigures(4, 6)
                .DataBodyRange(i, 164).Value = d105TicksheetNightsFigures(4, 7)
                .DataBodyRange(i, 165).Value = d105TicksheetNightsFigures(4, 8)
                .DataBodyRange(i, 166).Value = d105TicksheetNightsFigures(4, 9)
                .DataBodyRange(i, 167).Value = d105TicksheetNightsFigures(4, 10)

        End If

    Next i
End With

最佳答案

尝试以下代码（从第二列开始传输数据）：

Const tRows as Long = 4
Const tColumns as Long = 10

Dim i as Long, rowCounter as Long, colCounter as Long, outStart as Long
outStart = 132

With ActiveSheet.ListObjects("d105WeeklyTable")
    For i = 1 To .ListRows.Count
        If .DataBodyRange(i, 2).Value = wkComm Then
            For rowCounter = 1 to tRows
                For colCounter = 2 to tColumns
                    .DataBodyRange(i, outStart).Value = d105TicksheetNightsFigures(rowCounter, colCounter)
                    outStart = outStart + 1
                Next colCounter
            Next rowCounter
        End If
    Next i
End With

关于excel - 再次与嵌套循环VBA混淆，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50603733/