excel - 基于列值复制行的循环

Question

如果 qty 中的值，我想复制行大于 1 的列.它应该重复单元格中的值的次数。然后我想将结果复制到不同的 sheet并编辑 qty柱子。下面的代码做到了，但 qty 的标题列已更改。我该如何解决这个问题并使其更有效率？

Option Explicit

Sub generateDups()
    Worksheets("Sheet2").Columns(1).ClearContents
    Worksheets("Sheet2").Columns(2).ClearContents
    Worksheets("Sheet2").Columns(3).ClearContents
    Worksheets("Sheet2").Columns(4).ClearContents

    Dim xRow As Long
    Dim inNum As Variant
    xRow = 1

    Do While (Cells(xRow, "A") <> "")
        inNum = Cells(xRow, "D")
        If ((inNum > 1) And IsNumeric(inNum)) Then
            Range(Cells(xRow, "A"), Cells(xRow, "D")).Copy
            Range(Cells(xRow + 1, "A"), Cells(xRow + inNum - 1, "D")).Select
            Selection.Insert Shift:=xlDown
            xRow = xRow + inNum - 1
        End If
        xRow = xRow + 1
    Loop

    copyAndEdit
End Sub



Sub copyAndEdit()
    Dim cValue As String
    Dim rowIndex As Integer
    Dim destRow As Integer
    Dim lastRow As Long, lastCol As Long
    Dim srcSheet As Worksheet
    Dim destSheet As Worksheet

    Set srcSheet = ThisWorkbook.Worksheets("Sheet1")
    Set destSheet = ThisWorkbook.Worksheets("Sheet2")
    destRow = 0
    
    With srcSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For rowIndex = 1 To lastRow
            cValue = .Cells(rowIndex, 2).Value
            destRow = destRow + 1
            destSheet.Cells(destRow, 1) = .Cells(rowIndex, 1)
            destSheet.Cells(destRow, 2) = .Cells(rowIndex, 2)
            destSheet.Cells(destRow, 3) = .Cells(rowIndex, 3)
            destSheet.Cells(destRow, 4) = .Cells(rowIndex, 4) 
        Next rowIndex
    End With
        
    Set srcSheet = Nothing

    With destSheet
        destRow = 0
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For rowIndex = 1 To lastRow
            destRow = destRow + 1
            If destSheet.Cells(destRow, 4).Value > 1 Then
                destSheet.Cells(destRow, 4).Value = 1
            Else
                destSheet.Cells(destRow, 4).Value = .Cells(rowIndex, 4).Value
            End If
        Next rowIndex
        
    End With
        
    Set destSheet = Nothing
End Sub

当前数据:

我得到了什么:

预期:

Answer 1

这部分会导致问题，因为您正在编写代码第二部分的标题:

destRow = 0

'.....

For rowIndex = 1 To lastRow
    destRow = destRow + 1
    If destSheet.Cells(destRow, 4).Value > 1 Then
        destSheet.Cells(destRow, 4).Value = 1

因此，您将 destRow 设置为 0(即 destRow = 0 )。在第一次迭代中，它将是第 1 行( destRow = destRow + 1 -> destRow = 1 )。并且由于单元格返回一个大于 1 的值，因为它不是空的或单元格中有 0，它将在第一行打印 1(即“数量”将被覆盖)。
一种方法(如果你总是有 numeric values )是使用 Isnumeric像这样(替换这部分 If destSheet.Cells(destRow, 4).Value > 1 Then ):

If IsNumeric(destSheet.Cells(destRow, 4).Value) > 1 Then

另一种方法是跳过第一个标题是忽略迭代中的第一行:

If destSheet.Cells(destRow, 4).Value > 1 And destRow <> 1 Then 'Could be >1 also

旁注，我还将在这部分使用工作表引用并声明工作表1:

Do While (srcSheet.Cells(xRow, "A") <> "")