带有列条件的 Excel 宏将一张表中的两行复制到另一张表中

Question

我需要一个宏来搜索一列中的字符串；找到该字符串后，它会复制找到该字符串的行以及该行上方的行。

例如：

在第 5 列中搜索“boy”；如果在第 6 行中找到，则复制第 6 行和第 5 行；然后在第 5 列中搜索下一个“boy”。

我怎样才能做到这一点？

Answer 1

这是你的宏

Sub Findining()

    Dim Col As Range
    Dim fs As Worksheet
    Dim s As String
    Dim ws As Worksheet
    Dim r As Range

    Set fs = Sheets(ActiveSheet)
    Set Col = Application.InputBox("Select Column to Look Through", Type:=8)
    If Col.Columns.Count > 1 Then
      Do Until Col.Columns.Count = 1
        MsgBox "You can only select 1 column"
        Set Col = Application.InputBox("Select Column to Compare", Type:=8)
      Loop
    End If

    s = InputBox("Enter string to search for:", "Enter String")
    Set ws = Sheets(fs.Index + 1)
    c = Split(Col.Address, "$")(1)

    For i = 1 To fs.Range(c & Rows.Count).End(xlUp).Row
        Set r = fs.Range(c & i)
        If StrComp(r, s, vbTextCompare) = 0 Then
            fs.Rows(r.Row & ":" & r.Row).Copy
            ws.Activate
            ws.Rows(ws.Range(c & Rows.Count).End(xlUp).Row + 1 & ":" & ws.Range(c & Rows.Count).End(xlUp).Row + 1). _
                PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
        Set r = Nothing
        fs.Activate
    Next i

End Sub