Macro that searches for a various strings in an Excel column, help

[ivopld]

Hi all,

I found a macro that searches for a particular string in an Excel column and highlights the rows where that string appears.

I actually would like to search for multiple strings, around 200 or so instead of only looking for one. I'm assuming I'd need to create an array to do this but I'm not sure how to go about this.. or is there some built in function I can use?

Here is the macro:

Please Login or Register  to view this content.

Any help would be apprecieated.

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly and change the QUOTE tags to CODE tags.

Thanks.

[broro183]

hi,

Tusha Mehta provides an alternative approach in identifying the range, by building it as a range rather than as an address string, in this link: http://www.tushar-mehta.com/excel/tips/findall.html

I have modified your code to use Tusha's function & loop through all the strings you need to find (using a list in column A on a new sheet - see macro). I suggest that you rename your variables for greater clarity. The below is untested but should point you in the general direction...

Please Login or Register  to view this content.

hth
Rob

[ivopld]

Thanks Rob, but I'm having some issues with the code. When I create the macro I get a compiler error 'Sub or Function not defined' for the FindAll function. Should I be declaring this somewhere?

Also, I'm not quite sure where to specify the strings that I'd be looking for. For example, if I want to search for the strings Jack, Stacy, and Susan where would I specify them exactly?

[shg]

You'd find the code for FindAll at the link Rob posted.

[broro183]

ivopld,
Ooopps, sorry about that!
The below section of code builds the list of strings to search for. To make it work you need to create a sheet in your file called "List of Search Strings" & then list Jack, Stacy, and Susan in cells A1:A3 of the new sheet. I consider that this approach has the advantages of; visibility within your file (no need to view the macro code), easy to update in the file & flexible due to the use of LastRow resulting in no need to change the macro when the list size changes (provided there are no blank cells in the list).

Please Login or Register  to view this content.

Shg, Thanks for the correction/pointing the OP in the right direction :-)

hth
Rob