Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need a macro that searches for a string in a column; on finding that string it copies the row in which that string is found as well as the row above that.

For example:

Search for "boy" in column 5; if found in row 6, copy row 6 and row 5; then search for the next "boy" in column 5.

How can I do this?

share|improve this question
    
where do you want to paste the copied row? – user222864 Sep 5 '13 at 9:23
    
to the next sheet – Kisembo O Julius Sep 5 '13 at 11:03

heres is your macro

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
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .