Hi Biff, thank you for your input.
Here's the code that I'm using courtesy of Jindon. For anyone else that's
looking to extract text within text this is perfect.
Sub FindAndCopy()
Dim r As Range, i As Long
Dim Phrase
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Phrase = Array("gift", "check", "wrong")
With ws1
For Each r In Intersect(.Range("r:x"), .UsedRange)
For i = LBound(Phrase) To UBound(Phrase)
If InStr(1, r, Phrase(i), vbTextCompare) 0 Then
r.EntireRow.Copy _
ws2.Range("A65536").End(xlUp).Offset(1, 0)
Exit For
End If
Next
Next
End With
MsgBox "done"
Application.CutCopyMode = False
End Sub
"Biff" wrote:
Hi!
Since you want the entire row of data "copied" to another sheet I think you
should use filters. Based on your description this would require too many
formulas to be efficient.
Take a look he
http://contextures.com/tiptech.html
There you'll find just about everything you always wanted to know about
filters!
Biff
"Cathy Landry" wrote in message
...
Hi Biff,
You're right, I was not very explicit in what I need. I'm working with a
spreadsheet that has data in col a through aa. columns r/x contain
"notes"
regarding corp/purchase card expenditures. I need to look for certain
words
within those two columns and copy the entire row/s to another sheet. We
want
to look at those individuals that are making purchases that fall outside
of
our company's policies and procedures. A few of the keywords would be
"gift", "personal", "party", "error", "check" etc.
Thank you!
Cathy
"Biff" wrote:
Hi!
Need a better explanation. Some examples would also help.
What does "I have two columns with text" have to do with it? Does that
mean
1 column may contain a keyword and the 2nd column may also contain a
keyword
and in those rows where both columns have keywords you want those 2
entries
extracted to another location?
Biff
"Cathy Landry" wrote in message
...
Hello,
I have two columns with text. I'd like take those rows that contain
multiple "keywords" and put them on a separate sheet. For example, a
few
of
the keywords would be: gift, check, personal, error and if that cell
contained any of those keywords then it would be put on a separate
sheet.
Is
this possible?
Thank you!