excel - 从 For Next 循环中剪切和粘贴的代码。我怎样才能专门使用 For Each 循环来做到这一点？

Question

假设满足条件，我想使用 For Each 循环将整行从一个工作表剪切并粘贴到另一个工作表。

在这里，我在 For/Next 循环中执行此操作，但我知道这些循环比使用 For/Each 循环慢得多，这是我的偏好。我需要知道如何在下面的子“流程”中的 For/Each 循环中进行剪切和粘贴（明确表示）。

这是我目前正在使用的代码：

Dim wbm, wbr As Workbook, wsm, wsr, wsc As Worksheet, LastRowC, LastRowr As Integer, Match As Boolean, ColV, v As Range, vaddr As String

Option Explicit

Public Sub Initialize()

Application.ScreenUpdating = False: Application.Calculation = xlManual: Application.EnableEvents = False

'Must have the correct worksheet name below:

Set wbr = Workbooks("LST_102019_25590000-25590200_MULTIPLE_ACCRUAL ROLL FORWARD TEMPLATE V1.0.xlsm")
Set wsr = wbr.Sheets("Open Items")
Set wsc = wbr.Sheets("Closed Items")

LastRowr = wsr.Cells(Rows.Count, 1).End(xlUp).Row

Sort_WBSe

Application.ScreenUpdating = True: Application.EnableEvents = True

End Sub

Public Sub Sort_WBSe()

    wsr.AutoFilter.Sort.SortFields.Clear
    wsr.AutoFilter.Sort.SortFields.Add _
        Key:=Range("V7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortTextAsNumbers
    With wsr.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Process

End Sub


Public Sub Process()

Dim N As Integer, Amt, AmtNext, WBSe, WBSeNext As String
LastRowC = wsc.Cells(Rows.Count, 1).End(xlUp).Row


Dim i As Long: i = LastRowC + 1 'Set the next row of the Closed Items sheet

'Col V is the WBSe column

'Note: This loop works from the bottom up.


With wsr

    For N = LastRowr To 7 Step -1

        WBSe = .Cells(N, "V"): Amt = .Cells(N, "V").Offset(0, -3)
        WBSeNext = .Cells(N, "V").Offset(-1, 0): AmtNext = .Cells(N, "V").Offset(-1, -3) * -1

        If WBSeNext = WBSe And (AmtNext) = Amt Then
            .Cells(N, "V").EntireRow.Cut wsc.Cells(i, "A") 'Cut the Row and Paste it to the Closed Items sheet
             If .Cells(N, "V") = "" Then
                .Cells(N, "V").EntireRow.Delete 'Delete the Blank Row
             End If

             i = i + 1

            .Cells(N - 1, "V").EntireRow.Cut wsc.Cells(i, "A") 'Cut the Row and Paste it to the Closed Items sheet
             If .Cells(N - 1, "V") = "" Then
                .Cells(N - 1, "V").EntireRow.Delete 'Delete the Blank Row
             End If
             i = i + 1
             N = N - 1
       End If

    Next
End With

End Sub

标签： excelvbafor-loopforeachnext