Find Multiple Words/Values In ExcEL

January 9, 2014 at 12:51:02
Specs: Windows 7
I have an excel spreadsheet with multiple lines of data; and within one column I need it to find every line that has a specific word in it and move that row to another tab. I have multiple words to seach for. The below code is close, but it stops after it finds the first instance of the word in the list. What I would actually like it to do, instead of copying to another sheet is color that cell a particular color.
Any assistance would be appreciated.

Sub GeneFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
 Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column D, copy it top the next row in Sheet2
  With Sheets(1).Columns("D")
    For gName = 2 To srchLen
      Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
        If Not g Is Nothing Then
          nxtRw = Sheets(2).Range("D" & Rows.Count).End(xlUp).Row + 1
          g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
        End If
  End With
End Sub

message edited by Mloftin66

See More: Find Multiple Words/Values In ExcEL

Report •

March 27, 2014 at 10:26:43
Does anyone have an answer for the above?

Report •
Related Solutions

Ask Question