Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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? –  vba4all Sep 5 '13 at 9:23
    
to the next sheet –  Kisembo O Julius Sep 5 '13 at 11:03

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.