Thread: [SOLVED] Searching a cell with multiple words

[SOLVED] Hi. I posted a problem on another forum (computing.net), and got a part resolution.

Basically, i have a piece of code that does the following:

1 - Sheet 1 Row 1 contains Column Headings.
2 - Sheet 2 is the destination sheet for the copied rows.
3 - You want the same Column Headings on Sheet 2 that are on Sheet 1.
4 - Sheet 3 Column A contains the list of names to search for, starting in A2.

Before running the following code, place the list of Names to be searched for in Sheet 3 Column A, starting in A2.

I suggest running the code in a backup copy of your workbook since macros can not be undone.

Option Explicit
Sub GeneFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
 Sheets(2).Cells.ClearContents
 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
    Next
  End With
End Sub

My further issue is with point 4 above. The code assumes that the cell in sheet 1 only contains a single word (i.e. a list of surnames for example). But i need the code to search through a paragraph that is contained in the cell and return the match values to sheet 2. (For example, instead of just looking at the 1st word or a single word in the cell in sheet 1, it scans the whole cell for the word, and once the word is matched, the whole row is returned to sheet 2). The list of words in sheet 3 will be single words (i,e, surnames)

I'm not sure if i am explaining it correctly, but hopefully someone can pick this up and modify the code above.

am happy to send over an example spreadsheet if required.

Thanks in advance

Hello nervous_pilchard,

Welcome to the Forum!

If you have a sample workbook you can post, it would make things easier. Does this workbook include all the words you want to search for?

Hi,
Try changing the xlWhole to xlPart in your code and see if that does what you want.