vba - 使用 IsEmpty 停止循环

Question

我发现一个线程直接应用于我试图在此处构建的代码 Excel VBA: Loop through cells and copy values to another workbook 。

Sub test()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim CurCell_1 As Range, CurCell_2 As Range
Dim Ran As Range
Dim Group As Range, Mat As Range

Application.ScreenUpdating = True

Set ws1 = ActiveWorkbook.Sheets("Scrap")
Set ws2 = ActiveWorkbook.Sheets("FC Detail")

For Each Mat In ws1.Range("E:E")
    Set CurCell_2 = ws2.Range("F8")
    For Each Group In ws1.Range("E:E")
        Set CurCell_1 = ws1.Cells(Group.Row, Mat.Column)
        If Not IsEmpty(CurCell_2) Then
            CurCell_2.Value = CurCell_1.Value
        End If
    Next
Next

End Sub

此代码有一个异常，它会不断循环。

我认为 If Not IsEmpty 将是 VBA 的描述符，一旦到达列表末尾就会停止程序。

Answer 1

继我的评论之后，试试这个。这样会快很多

Sub Test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim CurCell_1 As Range, CurCell_2 As Range
    Dim Group As Range, Mat As Range, Ran As Range
    Dim lRow As Long

    Set ws1 = ActiveWorkbook.Sheets("Scrap")
    Set ws2 = ActiveWorkbook.Sheets("FC Detail")

    With ws1
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        Set Ran = .Range("E1:E" & lRow)

        For Each Mat In Ran
            Set CurCell_2 = ws2.Range("F8")
            For Each Group In Ran
                Set CurCell_1 = .Cells(Group.Row, Mat.Column)
                If Not IsEmpty(CurCell_2) Then
                    CurCell_2.Value = CurCell_1.Value
                End If
            Next
        Next
    End With
End Sub