Find multiple INSTANCES of words in Excel

July 29, 2011 at 08:12:06
Specs: Windows XP
Hi Guys,

There was a pretty good post on this ( some time ago. My question is, I'm basically trying to have a list of values in one sheet 1, find those values in the particular column, then return the results of that search to a blank sheet (as referenced in the link). One thing I do not know how to do is make the macro work so that it finds multiple instances of the same word. For example, if I'm looking for the word "apple" in a sheet with 20 rows, I want the macro to copy and paste each row where the word "apple" is found in the corresponding column. Can anyone assist with this?

Below is the code kindly provided by DerbyDad03. Just need it to work with multiple instances of the search criteria. Thank you!

Option Explicit
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

See More: Find multiple INSTANCES of words in Excel

Report •

July 29, 2011 at 09:18:13
See this thread where the same question was asked about the same piece of code:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 29, 2011 at 10:08:34
Genius! This is what I needed to know. Thanks very much!

Report •

Related Solutions

Ask Question