vba - 如果单元格标记为完成，则宏将行复制到新列

Question

我试图学习如何编写一个宏，当 H 列中的单元格被标记为“完成”时，它会复制整行并将其粘贴到一个名为“完成”的新页面中，并从我的当前页面中删除该行。

我尝试自己录制它，但一直打破它。我不知道我是否做对了。

到目前为止，这是我的代码:

Sub Completed_Move()
    ' Completed_Move
    Macro Dim srchrng As Range, row As Range

    Set srchrng = Range("H1,H500")
    'For each cel in srchrng
    If InStr(1, cel.Value) = Complete Then
        Activecell.Offset(0, -7).Range("A1:N1").Select
        Selection.Cut
        Sheets("Completed").Select
        Activecell.Select
        ActiveSheet.Paste Sheets("Current").Select
        Activecell.Rows("2:1").EntireRow.Select
        Selection.Delete Shift:=xlUp
        Activecell.Offset(2, 7).Range("A1").Select
    End if
End Sub

Answer 1

正如您在评论中所说，如果您有一条与此一样明确的错误消息:

Expected End Sub

这意味着您的代码缺少 End Sub关闭子!只需将其添加到代码的末尾即可。

这是您的代码(带有 End Sub ...)，经过改进:

Sub Completed_Move()
    'Completed_Move Macro
    Dim SrchRng As Range, RowRg As Range, CellRg As Range
    Dim wS1 As Worksheet, wSC As Worksheet
    Dim i as Long

    With ThisWorkbook
        Set wS1 = .Sheets("Sheet1")
        Set wSC = .Sheets("Completed")
    End With 'ThisWorkbook

    Set SrchRng = wS1.Range("H1:H500")
    For i = SrchRng.Cells(SrchRng.Rows.Count,1).Row To SrchRng.Cells(1,1).Row Step -1
        'If "Complete" is part of value in H
        If InStr(1, SrchRng.Cells(i,1).Value, "Complete") Then
            With wSC
                wS1.Range("A" & SrchRng.Cells(i,1).Row & ":N" & SrchRng.Cells(i,1).Row).Copy .Range("A" & .Range("A" & .Rows.Count).End(xlUp).Row + 1)
            End With 'wSC
            SrchRng.Cells(i,1).Rows("2:1").EntireRow.Delete Shift:=xlUp
        End If
    Next i
End Sub