Is it possible for a macro to search for a phrase and export if true?

[ashleywanless]

Hi,

I have a large sheet which tracks initiatives. I would like to write some code so that a user can click on a button which opens a search box, they can then enter a phrase. What the macro will do is search the data (Columns A-AA) and where it finds a match in text to the phrase it exports that whole row to a new sheet1. It also needs to not produce duplicates as it is possible for the phrase to appear twice in the same row. Column A has a unique identifier if this helps in the process.

If anyone has any ideas on if this is possible and how to progress i would be grateful

Ashley

 

[Domenic]

Assuming that Sheet1 contains the source data, and Row 1 contains the headers, the following will copy the filtered data to Sheet2...

Sub test()

Dim x As String
Dim WSO As Worksheet
Dim WSN As Worksheet
Dim LastColumn As Long

Application.ScreenUpdating = False

Do
    x = Application.InputBox("Please enter your search term or phrase...", "Search Term/Phrase")
    If TypeName(x) = "Boolean" Then Exit Sub
Loop Until x <> ""

Set WSO = ActiveWorkbook.Worksheets("Sheet1")
Set WSN = ActiveWorkbook.Worksheets("Sheet2")

WSO.Activate
WSN.Cells.Clear

With WSO.UsedRange
    LastColumn = .Columns.Count
    WSO.Cells(2, LastColumn + 2).FormulaR1C1 = "=MATCH(""*" & x & "*"",RC1:RC" & LastColumn & ",0)"
    .AdvancedFilter Action:=xlFilterInPlace, criteriarange:=Cells(1, LastColumn + 2).Resize(2, 1)
    .Copy Destination:=WSN.Cells(1, 1)
End With

WSO.Activate
WSO.ShowAllData
WSO.Columns(LastColumn + 2).Clear
WSN.Activate

Application.ScreenUpdating = True

End Sub