再次与嵌套循环VBA混淆(Confused with nested Loops VBA Again)

再次与嵌套循环VBA混淆(Confused with nested Loops VBA Again)

我再一次陷入困境，应该是一些非常基本的东西。 我知道这个问题有一个更快的解决方案，但我似乎无法让循环正确。

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

源表有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

Once again I've got myself into trouble with something that should be rather basic. I know there is a faster solution to this problem, however I cant seem to get the loops right.

Essentially I would like to shorten this code, as I have a larger table and array to deal with next and my current solution below would require numerous lines.

The source table has 10 columns and 4 rows.. I need to read data from the source table and assign it to one line of the destination table "d105WeeklyTable"

Any help would be greatly appreciated.

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

最满意答案

试试这段代码（从第2栏开始传输数据）：

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

Try this code (transfers data starting from 2nd column):

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