Create a list in column G if specific text found in cells in column A

[Barryj]

I'm am after some VBA code that will look at cells in column A for text that matches cell M1, the value in M1 will change and the macro will search based if this value appears somewhere in the text of cells in column A.

This is and example of the type of text that will be in column A: "The information is correct $A$8 is valid"

Cell M1 will have the value $A$8

So I want to build a list in column G with any cell in column A that contains the text in cell M1

Thanks for any assistance.

[mancubus]

?



Sub vbax_68376_filter_copy_xlNo()


With ActiveSheet
.Range("A1").AutoFilter Field:=1, Criteria1:="=*" & .Range("M1").Value & "*", Operator:=xlAnd
.Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy 'exclude header
.Range("G1").PasteSpecial
.AutoFilterMode = False
.Range("G1").RemoveDuplicates Columns:=1, Header:=xlNo
End With


End Sub




Sub vbax_68376_filter_copy_xlYes()


With ActiveSheet
.Range("A1").AutoFilter Field:=1, Criteria1:="=*" & .Range("M1").Value & "*", Operator:=xlAnd
.Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy 'include header
.Range("G1").PasteSpecial
.AutoFilterMode = False
.Range("G1").RemoveDuplicates Columns:=1, Header:=xlYes
End With


End Sub

[Barryj]

Mancubus, thank you very much, the code you provided does exactly what I was hoping for, much appreciated.

[mancubus]

you are welcome. i'm glad it helped.