Excel VBA行重复逻辑错误排查：财期行重复后数组值异常

我有一个Excel文件，其中I列标识每个零件号行的财期到期月。需求为：当I列值为Q1、Q2、Q3、Q4、H1、H2、FY时，按对应数组重复行（如Q1需保留原行并重复2次，生成3行，分别对应October、November、December；H1需生成6行，对应6个月份），同时将F列单价均分到各行。但当前代码执行后，数组倒数第二个值会重复出现（如Q1行显示October、November、November）。以下是我编写的VBA代码：

Sub DuplicateRowsAndAdjustTotalValue()
    Dim ws As Worksheet
    Dim lastRow As Long, newRow As Long
    Dim fiscalMonth As String, repeatTimes As Integer
    Dim totalValue As Double
    Dim i As Integer
    Dim j As Integer
        
    Set ws = ThisWorkbook.Worksheets("Go Get")
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    'Arrays
    q1 = Array("October", "November", "December")
    q2 = Array("January", "February", "March")
    q3 = Array("April", "May", "June")
    q4 = Array("July", "August", "September")
    h1 = Array("October", "November", "December", "January", "February", "March")
    h2 = Array("April", "May", "June", "July", "August", "September")
    fy = Array("October", "November", "December", "January", "February", "March", "April", "May", "June", "July", "August", "September")
    j = 1
    
    ' Loop through each row in reverse order to avoid issues with duplicated rows
    For newRow = lastRow To 2 Step -1
        fiscalMonth = ws.Cells(newRow, "I").Value
        unitPrice = ws.Cells(newRow, "F").Value
        
        Select Case fiscalMonth
            Case "Q1", "Q2", "Q3", "Q4"
                repeatTimes = 3
            Case "H1", "H2"
                repeatTimes = 6
            Case "FY24"
                repeatTimes = 12
            Case Else
                repeatTimes = 1
        End Select
        
        If fiscalMonth = "Q1" Then 'Q1
            ws.Cells(newRow, "F").Value = unitPrice / repeatTimes
            ws.Cells(newRow, "I").Value = q1(0)
            For i = 1 To repeatTimes - 1
                ws.Cells(newRow, "I").Value = q1(j)
                ws.Rows(newRow).Copy
                ws.Rows(newRow).Insert Shift:=xlDown
                j = j + 1
            Next i
            j = 0
        End If
        
        If fiscalMonth = "Q2" Then   'Q2
            ws.Cells(newRow, "F").Value = unitPrice / repeatTimes
            ws.Cells(newRow, "I").Value = q2(0)
            For i = 1 To repeatTimes - 1
                ws.Cells(newRow, "I").Value = q2(j)
                ws.Rows(newRow).Copy
                ws.Rows(newRow).Insert Shift:=xlDown
                j = j + 1
            Next i
            j = 0
        End If
        
        If fiscalMonth = "Q3" Then    'Q3
            ws.Cells(newRow, "F").Value = unitPrice / repeatTimes
            ws.Cells(newRow, "I").Value = q3(0)
            For i = 1 To repeatTimes - 1
                ws.Cells(newRow, "I").Value = q3(j)
                ws.Rows(newRow).Copy
                ws.Rows(newRow).Insert Shift:=xlDown
                j = j + 1
            Next i
            j = 0
        End If
        
        If fiscalMonth = "Q4" Then    'Q4
            ws.Cells(newRow, "F").Value = unitPrice / repeatTimes
            ws.Cells(newRow, "I").Value = q4(0)
            For i = 1 To repeatTimes - 1
                ws.Cells(newRow, "I").Value = q4(j)
                ws.Rows(newRow).Copy
                ws.Rows(newRow).Insert Shift:=xlDown
                j = j + 1
            Next i
            j = 0
        End If
        
        If fiscalMonth = "H1" Then     'H1
            ws.Cells(newRow, "F").Value = unitPrice / repeatTimes
            ws.Cells(newRow, "I").Value = h1(0)
            For i = 1 To repeatTimes - 1
                ws.Cells(newRow, "I").Value = h1(j)
                ws.Rows(newRow).Copy
                ws.Rows(newRow).Insert Shift:=xlDown
                j = j + 1
            Next i
            j = 0
        End If
        
        If fiscalMonth = "H2" Then       'H2
            ws.Cells(newRow, "F").Value = unitPrice / repeatTimes
            ws.Cells(newRow, "I").Value = h2(0)
            For i = 1 To repeatTimes - 1
                ws.Cells(newRow, "I").Value = h2(j)
                ws.Rows(newRow).Copy
                ws.Rows(newRow).Insert Shift:=xlDown
                j = j + 1
            Next i
            j = 0
        End If
        
        If Left(fiscalMonth, 2) = "FY" Then   'FY
            ws.Cells(newRow, "F").Value = unitPrice / repeatTimes
            ws.Cells(newRow, "I").Value = fy(0)
            For i = 1 To repeatTimes - 1
                ws.Cells(newRow, "I").Value = fy(j)
                ws.Rows(newRow).Copy
                ws.Rows(newRow).Insert Shift:=xlDown
                j = j + 1
            Next i
            j = 0
        End If
        
    Next newRow
    
    Application.ScreenUpdating = True
End Sub

出现重复值的核心原因是行修改与复制的顺序错误，以及索引变量j的对应逻辑混乱：

1. 先修改原行的I列值为数组的第j个元素，再复制插入新行，导致新插入的行直接继承了修改后的值，同时原行的初始月份（数组第0位）被覆盖丢失。
2. 以Q1为例，原本要保留原行为October，插入November和December，但原代码的操作顺序导致原行的October被立刻改成November，复制插入的行也是November，后续又把原行改成December并插入，最终出现月份错位或重复的情况。
3. 代码存在大量重复逻辑，每个财期的处理代码几乎一致，既冗余又容易出错。

优化后的代码调整了操作顺序，合并了重复逻辑，确保每个行的月份与数组元素一一对应：

Sub DuplicateRowsAndAdjustTotalValue()
    Dim ws As Worksheet
    Dim lastRow As Long, currentRow As Long
    Dim fiscalMonth As String, repeatTimes As Integer
    Dim unitPrice As Double
    Dim monthArr As Variant
    Dim i As Integer
    
    Set ws = ThisWorkbook.Worksheets("Go Get")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    ' 从最后一行倒序处理，避免插入行影响循环
    For currentRow = lastRow To 2 Step -1
        fiscalMonth = ws.Cells(currentRow, "I").Value
        unitPrice = ws.Cells(currentRow, "F").Value
        
        ' 根据财期获取对应的月份数组和重复次数
        Select Case fiscalMonth
            Case "Q1"
                monthArr = Array("October", "November", "December")
                repeatTimes = 3
            Case "Q2"
                monthArr = Array("January", "February", "March")
                repeatTimes = 3
            Case "Q3"
                monthArr = Array("April", "May", "June")
                repeatTimes = 3
            Case "Q4"
                monthArr = Array("July", "August", "September")
                repeatTimes = 3
            Case "H1"
                monthArr = Array("October", "November", "December", "January", "February", "March")
                repeatTimes = 6
            Case "H2"
                monthArr = Array("April", "May", "June", "July", "August", "September")
                repeatTimes = 6
            Case Else
                ' 判断是否为FY开头的财期
                If Left(fiscalMonth, 2) = "FY" Then
                    monthArr = Array("October", "November", "December", "January", "February", "March", _
                                    "April", "May", "June", "July", "August", "September")
                    repeatTimes = 12
                Else
                    ' 非目标财期，跳过处理
                    GoTo NextRow
                End If
        End Select
        
        ' 先调整原行的单价和月份（数组第0位）
        ws.Cells(currentRow, "F").Value = unitPrice / repeatTimes
        ws.Cells(currentRow, "I").Value = monthArr(0)
        
        ' 插入剩余的行，从数组第1位开始
        For i = 1 To repeatTimes - 1
            ' 复制原行，在当前行下方插入
            ws.Rows(currentRow).Copy
            ws.Rows(currentRow + i).Insert Shift:=xlDown
            ' 修改新插入行的月份为数组对应索引
            ws.Cells(currentRow + i, "I").Value = monthArr(i)
        Next i
        
NextRow:
    Next currentRow
    
    Application.ScreenUpdating = True
End Sub

1. 调整操作顺序：先设置原行为数组第0位的月份，再复制原行插入到指定位置，最后修改新插入行的月份为数组对应索引，确保每个行的月份正确对应。
2. 合并重复逻辑：通过Select Case直接获取对应的月份数组和重复次数，避免重复写相同的循环代码，提升可维护性。
3. 索引对应准确：循环变量i直接对应数组的索引（从1到repeatTimes-1），确保每个新插入行的月份和数组一一对应，不会出现错位。
4. 明确插入位置：使用currentRow + i作为插入位置，确保每次插入的行紧跟在原行之后，顺序正确。

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