如何修改VBA代码，使Flat File中C列值仅重复3次而非随D列行数重复？

我编写了如下VBA代码，用于将Dashboard_Test工作表的数据整理到Flat File工作表中：

Sub RepeatData3()

    ' Declare variables
    Dim dashboard As Worksheet
    Dim flatfile As Worksheet
    Dim lastRowC As Long
    Dim lastRowD As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim Row_Counter As Long
    
    ' Set worksheet variables
    Set dashboard = ThisWorkbook.Sheets("Dashboard_Test")
    Set flatfile = ThisWorkbook.Sheets("Flat File")
    
    ' Find last row with data in column C and D of Dashboard tab
    lastRowC = dashboard.Cells(dashboard.Rows.Count, "C").End(xlUp).Row
    lastRowD = dashboard.Cells(dashboard.Rows.Count, "D").End(xlUp).Row
    
    Row_Counter = 8
    
    ' Loop through each row in column C of Dashboard tab
    For i = 1 To lastRowC
        ' Check if row has data
        If Len(dashboard.Range("C" & i).Value) > 0 Then
            ' Loop through each row of column D of Dashboard tab
            For k = 1 To lastRowD
                ' Check if row has data and does not contain "Total"
                If Len(dashboard.Range("D" & k).Value) > 0 And _
                   UCase(dashboard.Range("D" & k).Value) <> "TOTAL" Then
                   
                    ' Repeat data 30 times in column A and B of Flat File tab
                    For j = 1 To 3
                        flatfile.Range("A" & Row_Counter).Value = dashboard.Range("C" & i).Value
                        flatfile.Range("B" & Row_Counter).Value = dashboard.Range("D" & k).Value
                        Row_Counter = Row_Counter + 1
                    Next j
                'Else
                    ' Break out of For loop with J counter if cell is blank or contains "Total"
                    'Exit For
                End If
            Next k
        End If
    Next i
      
End Sub

当前代码运行后，Flat File工作表中来自Dashboard_Test D列的值会重复3次，但C列的值重复次数等于D列的有效行数（非空且不含"Total"），而非我预期的仅重复3次。请问需要修改代码的哪些部分，才能让Flat File中的输出满足C列值仅重复3次的要求？

问题根源 #

原代码的循环嵌套顺序错误：先遍历D列所有有效行，再在每行下重复3次C列值，导致单个C列值会和所有D列有效值配对，总重复次数为「D列有效行数 ×3」，不符合预期。

修改后的代码 #

Sub RepeatData3()

    ' Declare variables
    Dim dashboard As Worksheet
    Dim flatfile As Worksheet
    Dim lastRowC As Long
    Dim lastRowD As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim Row_Counter As Long
    
    ' Set worksheet variables
    Set dashboard = ThisWorkbook.Sheets("Dashboard_Test")
    Set flatfile = ThisWorkbook.Sheets("Flat File")
    
    ' Find last row with data in column C and D of Dashboard tab
    lastRowC = dashboard.Cells(dashboard.Rows.Count, "C").End(xlUp).Row
    lastRowD = dashboard.Cells(dashboard.Rows.Count, "D").End(xlUp).Row
    
    Row_Counter = 8
    
    ' Loop through each row in column C of Dashboard tab
    For i = 1 To lastRowC
        ' Check if row has data
        If Len(dashboard.Range("C" & i).Value) > 0 Then
            ' 先执行3次C列值的重复周期
            For j = 1 To 3
                ' 遍历所有D列有效行，完成当前周期的配对
                For k = 1 To lastRowD
                    If Len(dashboard.Range("D" & k).Value) > 0 And _
                       UCase(dashboard.Range("D" & k).Value) <> "TOTAL" Then
                        flatfile.Range("A" & Row_Counter).Value = dashboard.Range("C" & i).Value
                        flatfile.Range("B" & Row_Counter).Value = dashboard.Range("D" & k).Value
                        Row_Counter = Row_Counter + 1
                    End If
                Next k
            Next j
        End If
    Next i
      
End Sub

关键修改点 #

1. 调整循环嵌套顺序：将重复3次的For j = 1 To 3放到D列循环的外层，确保每个C列值先触发3次完整的D列遍历周期，最终C列值仅按预期重复3次（每次重复对应所有D列有效值）。
2. 移除原代码中未启用的注释逻辑，保持代码整洁。

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