VBA嵌套循环困惑：如何简化批量赋值代码？

Hey Lee, I get it—dealing with repetitive VBA code like this is frustrating, especially when you know there's a cleaner way to handle it. Let's break down your problem and rewrite this with nested loops (and even a bulk assignment trick) to make it scalable for larger datasets later.

The Core Pattern in Your Original Code #

Looking at your code, we can spot a clear, repetitive pattern:

• You’re looping through rows in d105WeeklyTable to find where column 2 matches wkComm
• For each matching row, you’re copying 9 values from each row of d105TicksheetNightsFigures (columns 2 to 10) into consecutive columns in the table—starting at column 132 for the first source row, 141 for the second, 150 for the third, and 159 for the fourth.

Optimized Code with Nested Loops #

This version uses nested loops to eliminate all the repetitive assignment lines. It’s easier to maintain and will scale seamlessly if you need to handle more rows/columns later:

With ActiveSheet.ListObjects("d105WeeklyTable")
    ' Loop through each row in the target table
    For i = 1 To .ListRows.Count
        If .DataBodyRange(i, 2).Value = wkComm Then
            ' Starting column in target table for the first source row
            Dim targetColStart As Long
            targetColStart = 132
            
            ' Loop through each row in your source array (1 to 4 rows)
            For rowSrc = 1 To 4
                ' Loop through each column in the source array (columns 2 to 10)
                For colSrc = 2 To 10
                    ' Calculate the target column position
                    Dim targetCol As Long
                    targetCol = targetColStart + (rowSrc - 1) * 9 + (colSrc - 2)
                    
                    ' Assign the value from source to target
                    .DataBodyRange(i, targetCol).Value = d105TicksheetNightsFigures(rowSrc, colSrc)
                Next colSrc
            Next rowSrc
        End If
    Next i
End With

Even More Efficient: Bulk Range Assignment #

If you want better performance (critical for large datasets), you can assign entire ranges at once instead of looping through each cell. This cuts down on slow worksheet interactions, which is a big win in VBA:

With ActiveSheet.ListObjects("d105WeeklyTable")
    For i = 1 To .ListRows.Count
        If .DataBodyRange(i, 2).Value = wkComm Then
            ' Assign entire 1x9 ranges in one line each
            .DataBodyRange(i, 132).Resize(1, 9).Value = d105TicksheetNightsFigures(1, 2).Resize(1, 9).Value
            .DataBodyRange(i, 141).Resize(1, 9).Value = d105TicksheetNightsFigures(2, 2).Resize(1, 9).Value
            .DataBodyRange(i, 150).Resize(1, 9).Value = d105TicksheetNightsFigures(3, 2).Resize(1, 9).Value
            .DataBodyRange(i, 159).Resize(1, 9).Value = d105TicksheetNightsFigures(4, 2).Resize(1, 9).Value
        End If
    Next i
End With

Key Notes #

• Nested Loops Version: Adjusting for more source rows is as simple as changing For rowSrc = 1 To 4 to match your new row count. The math handles the target column offset automatically.
• Bulk Assignment Version: This is much faster for large datasets because it minimizes the number of times VBA talks to the worksheet (worksheet operations are far slower than memory-based array operations).

Both versions are way cleaner than your original code and will be easier to tweak when you move to larger tables or arrays.

内容的提问来源于stack exchange，提问作者Lee Read