Put Find code in Loop to find all matches

**SarahPintal** · 02-08-2010, 06:56 PM

hi guys,

Is there a way to put the below in a loop so that it finds all of the matches, not just a single match then move onto the next Ccell.

At the moment it is finding 'Sarah' (for example) and then copying the line then moving to 'Forum' when in fact there are multiple 'Sarah's, and I wanted all the Sarah's to be copied then for it to look for the next in the list (Forum).

as a side note it would really really be appreciated if you can educate\talk me through the looping code that you come up with as I would love nothing more than learn in all honestly.

Thanks a lot in advance,

FYI: the below code works atm , just not how i would like it.

Please Login or Register  to view this content.

**JBeaucaire** · 02-08-2010, 08:13 PM

I don't think you need a loop to find all the "Sarah" values in column A and copy the rows. You need to turn on the Data > Filter > Autofilter and filter column A by the name "Sarah". Then you can copy all visible rows to the other sheet, turn off the filter and you got them all.

If you attached a sample workbook with this code with a good "sample results" page so we can see the before/after, we can help more accurately.

**SarahPintal** · 02-08-2010, 08:19 PM

Thanks for your response,

That is one way of doing it if there was only 1 item to find, though there may be 20 items to find (itam = a differnt name), hence why I thought a loop would be the best and some how setting a variable to the first cell address of the the item found.. then the code will loop through all of the range trying to find any other mathes of that item until it gets back to the first cell address..

hope that made sense

**shg** · 02-08-2010, 08:43 PM

Maybe like this, Sarah.

The variable sAddr caches the address of the first cell found containing each value. The inner loop iterates until it finds the same cell again. I didn't set up anything to test it with, so it's ... untested.

Please Login or Register  to view this content.

**SarahPintal** · 02-08-2010, 09:05 PM

Worked like a charm, Thanks shg,

I was working on some code that was more or less like yours, but it kept on failing, I think i can see why (a few things).

I am going to continue to compare what code i just did in the last 30 mins with your to keep learning..

Thanks