Excel 未正确找到最后一行

Question

我试图在工作表中找到最后一行作为我的宏的一部分。在此之前的一步将大量数据复制到工作表 4。当我到达这一步时，excel 错误地将最后一行标识为我的工作表中的某个随机行。我试过.Find和无数其他方法，似乎没有任何效果。

Sub Step19MatchStrike()

ActiveWorkbook.Save

Dim ws As Worksheet
Set ws = Worksheets(4)

Dim LastRowColumnA As Long

LastRowColumnA = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.Range("AA1").Value = "Strike Determination"
ws.Range("AA2").FormulaArray = "=IF((MATCH(E2,INDEX(Sheet3,(MATCH(A2,INDEX(Sheet3,,1),0)),5),0))>0,""To Keep"",""To Delete"")"
ws.Range("AA2").Copy ws.Range("AA3:AA" & LastRowColumnA)
ws.Columns(27).Value = ws.Columns(27).Value


End Sub

Answer 1

这段代码会做你想做的

Sub Step19MatchStrike()
    Dim ws As Worksheet

    '~~> Change as applicable
    Set ws = Sheet1

    Dim lRow As Long

    With ws
        '~~> Find the last row in col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("AA1").Value = "Strike Determination"

        '~~> Fill the formula in one go
        .Range("AA2:AA" & lRow).FormulaArray = YOURFORMULA
        .Columns(27).Value = .Columns(27).Value
    End With
End Sub

我只是好奇 Sheet3在你的公式中。这看起来不对
"=IF((MATCH(E2,INDEX(Sheet3,(MATCH(A2,INDEX(Sheet3,,1),0)),5),0))>0,""To Keep"",""To Delete"")"
编辑

使用自动填充

Sub Step19MatchStrike()
    Dim ws As Worksheet

    '~~> Change as applicable
    Set ws = Sheet1

    Dim lRow As Long

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("AA1").Value = "Strike Determination"
        .Range("AA2").FormulaArray = "=IF((MATCH(E2,INDEX(Sheet3,(MATCH(A2,INDEX(Sheet3,,1),0)),5),0))>0,""To Keep"",""To Delete"")"
        .Range("AA2").AutoFill Destination:=.Range("AA2:AA" & lRow), Type:=xlFillDefault
    End With
End Sub