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

ashleywanless

Board Regular
Joined
Aug 4, 2009
Messages
158
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,139
Assuming that Sheet1 contains the source data, and Row 1 contains the headers, the following will copy the filtered data to Sheet2...

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,920
Messages
5,447,295
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top