[SOLVED] Searching a cell with multiple words

**nervous_pilchard** · 12-20-2010, 11:55 AM

[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.

Please Login or Register  to view this content.

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

**Leith Ross** · 12-20-2010, 12:10 PM

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?

**nervous_pilchard** · 12-21-2010, 05:37 AM

hi. i have attached the spreadsheet. sheet 1 contains typical data from a call logging helpdesk.

There is a list of servers in sheet 3. I want to match any servers against the data in sheet 1.

To make the code work, i have put 2 server names in rows 8 and 17 in sheet 1 (column D)to show how it picks up values where there is only one word, and places them into sheet 2 when you run the macro.

So, what i need to happen, no matter where the server name sits in the cell, that it matches and puts the row in sheet 2.

If you could also make it search through column B and Column I as well for the same criteria (server name), that would be brilliant.

hope you can make it work!!

**MarvinP** · 12-21-2010, 08:56 AM

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

**nervous_pilchard** · 12-21-2010, 10:17 AM

Hey - thanks Marvin - you're a star. it seems such a simple modification, i feel like kicking myself!

but then i suppose you have to know what to change to be able to make the change!

thanks once again!