arrays - 在 VBA 中比较两个数组并添加行

Question

由于我对 VBA 相当陌生，所以我需要部分代码的帮助，这应该比较按升序排序的两个数组，如果其中一个数组中的值丢失，则应该将行添加到适当的表格并用零值填充到旁边的单元格中的缺失值。这是我到目前为止所得到的:

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For I = 3 To LastRow
    If Cells(I, 1) > Cells(I, 6) Then
        LastRow = LastRow + 1
        With Range("A" & I & ":C" & I)
            .Select
            .Insert Shift:=xlDown
        End With
        Range("A" & I) = Cells(I, 6)
        Cells(I, 2).Value = 0
    ElseIf Cells(I, 1).Value < Cells(I, 6).Value Then
        With Range("F" & I & ":H" & I)
            .Select
            .Insert Shift:=xlDown
        End With
        Range("F" & I) = Cells(I, 1)
        Cells(I, 7).Value = 0
    Else
    End If
 Next i

除了无效之外(这不是问题，因为两个数组都非常小)，这段代码的问题在于 LastRow:a) 随着添加的每一行而变化，b) 仅计算 array1 中的 LastRow，因此如果 array2 较大，则不会一直向下，c)如果单元格为空，即使我添加

，它似乎也会将带有空单元格的行添加到适当的数组中

If Not IsEmpty (Cells(i, 1)) And IsEmpty(Cells(i, 6)) Then 'next i

我知道解决方案可能在于定义两个数组并使用 LBound 到 Ubound，但我无法理解它。非常感谢您的帮助!

编辑:最后一行的事情现在似乎已经修复，但是我仍然无法以某种方式跳过空白单元格和最后一行中包含“总计”文本的单元格，因此与范围的其余部分不同，它没有进行排序。任何人有任何想法如何绕过这个？这就是代码现在的样子:

CurrentRow = 3
Do While CurrentRow <= LastRow
    If Cells(CurrentRow, 1) > Cells(CurrentRow, 6) Then
        If Not Cells(CurrentRow, 6).Value = "Grand Total" Or IsEmpty(Cells(CurrentRow, 6).Value) Then
            With Range("A" & CurrentRow & ":C" & CurrentRow)
                .Select
                .Insert Shift:=xlDown
            End With
            Range("A" & CurrentRow) = Cells(CurrentRow, 6)
            Cells(CurrentRow, 2).Value = 0
        End If
    ElseIf Cells(CurrentRow, 6) > Cells(CurrentRow, 1) Then
        If Not Cells(CurrentRow, 1).Value = "Grand Total" Or IsEmpty(Cells(CurrentRow, 1).Value) Then
            With Range("F" & CurrentRow & ":H" & CurrentRow)
                .Select
                .Insert Shift:=xlDown
            End With
            Range("F" & CurrentRow) = Cells(CurrentRow, 1)
            Cells(CurrentRow, 7).Value = 0
        End If
    Else
    End If
    With ActiveSheet
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    End With
    CurrentRow = CurrentRow + 1
    Debug.Print CurrentRow
Loop

编辑2:我终于明白了!我刚刚添加了另一个条件，如果发现值“总计”，则将行添加到相反的表中。无需担心空单元格!

CurrentRow = 3
Do While CurrentRow <= LastRow
    If Cells(CurrentRow, 1) > Cells(CurrentRow, 6) Then
        If Cells(CurrentRow, 6).Value = "Grand Total" Then
            With Range("F" & CurrentRow & ":H" & CurrentRow)
                .Select
                .Insert Shift:=xlDown
            End With
            Range("F" & CurrentRow) = Cells(CurrentRow, 1)
            Cells(CurrentRow, 7).Value = 0
        Else
            With Range("A" & CurrentRow & ":C" & CurrentRow)
                .Select
                .Insert Shift:=xlDown
            End With
            Range("A" & CurrentRow) = Cells(CurrentRow, 6)
            Cells(CurrentRow, 2).Value = 0
        End If
    ElseIf Cells(CurrentRow, 6) > Cells(CurrentRow, 1) Then
        If Cells(CurrentRow, 1).Value = "Grand Total" Then
            With Range("A" & CurrentRow & ":C" & CurrentRow)
                .Select
                .Insert Shift:=xlDown
            End With
            Range("A" & CurrentRow) = Cells(CurrentRow, 6)
            Cells(CurrentRow, 2).Value = 0
        Else
            With Range("F" & CurrentRow & ":H" & CurrentRow)
                .Select
                .Insert Shift:=xlDown
            End With
            Range("F" & CurrentRow) = Cells(CurrentRow, 1)
            Cells(CurrentRow, 7).Value = 0
        End If
    Else
    End If
    With ActiveSheet
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    End With
    CurrentRow = CurrentRow + 1
    Debug.Print CurrentRow
Loop

Answer 1

一个很好的整洁的问题，需要改变!以下是我对前两部分的想法:

a) 对于 for 循环，仅在启动循环时读取初始条件(在本例中为 Lastrow)，因此如果在循环期间更改 Lastrow，循环仍将仅运行到原始值。

要解决这个问题，您可以将其重组为 do while 循环。使用通用示例:

Sub loop_for()

    Dim intLoop As Integer
    Dim intEnd As Integer

    intEnd = 3

    For intLoop = 1 To intEnd 'this is fixed as soon as is triggered
        Debug.Print intLoop
        If intLoop = 2 Then intEnd = 4 'this has no effect on loop
    Next intLoop

'output is 1,2,3
End Sub

VS

Sub loop_while()

    Dim intLoop As Integer
    Dim intEnd As Integer

    intLoop = 1
    intEnd = 3

    Do While intLoop <= intEnd

        Debug.Print intLoop
        intLoop = intLoop + 1

        If intLoop = 2 Then intEnd = 4

    Loop

'output is 1,2,3,4
End Sub

b)为什么不直接评估两者并选择两者中较大的一个？

Sub lastrow()

    Dim lastrow As Long
    Dim lastrow1 As Long
    Dim lastrow2 As Long

    lastrow1 = ActiveSheet.Cells(.Rows.Count, "A").End(xlUp).Row
    lastrow2 = ActiveSheet.Cells(.Rows.Count, "F").End(xlUp).Row
    lastrow = Application.Max(lastrow1, lastrow2)

End Sub

c)这里已经失去了动力，希望其他人可以提供帮助。