为什么控件要直接"End Sub"？

Question

我使用了一个名为rng的变量作为Range。我找到了最后一行使用:

lastrow = tmpSheet.Cells(tmpSheet.Rows.Count, "A").End(xlUp).Row

,在Foundcell(Range格式)中找到一个单词后，使用:

Set Foundcell = tmpSheet.Range("A2:A" & lastrow).Find(What:="ABC")
Do Until Foundcell Is Nothing
      Set rng = tmpSheet.Range(Cells(1, 1), Cells(lastrow, 1))
       .
       .
        (Copy the row from a aheet to another)
       .
       .
errHandler:

End Sub

在Foundcell中获得值后，控件直接从"Set rng"行转到结束子。我不明白为什么会这样

Answer 1

如果您要在tmpsheet上获取范围Cells(1,1)到Cells(lastrow,1)，您应该将Set Rng行更改为:

Set rng = Range(tmpSheet.Cells(1,1), tmpSheet.Cells(lastrow,1))

。

Answer 2

您没有提供完整的代码，但这可能是您正在寻找的。

Sub Button1_Click()
    Dim tmpSheet As Worksheet
    Dim PasteSH As Worksheet
    Dim lastrow As Long
    Dim rng As Range
    Dim c As Range
    Set tmpSheet = Sheets("Sheet1")
    Set PasteSH = Sheets("Sheet2")
    With tmpSheet
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & lastrow)
    End With
    For Each c In rng.Cells
        If c = "ABC" Then
            c.EntireRow.Copy PasteSH.Cells(PasteSH.Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next c
End Sub