Find all/Select All/Replace all

**just_some_guy** · 05-13-2011, 02:34 PM

Good afternoon.

I have spent most of the day researching and trying a variety of things, but have yet to find something that works. I'm hoping someone here can once again teach me how to do this.

I have a spreadsheet that has been exported out of document direct. It lists each of the locations over two regions and the formatting is less than ideal. I've figured out how to extract the data I need, but I'm trying to automate the process. From start to finish, I have to sets of words that I use "Find all" for, copy all of them at once, and paste them to a new sheet. I use text to columns (which I did figure out the code for) to break down the information, and get rid of everything I don't need

Next, I find the second search word, copy all instances over to the new sheet, and then do a find and replace all for the words YTD Controlled W/O. I replace it so that all of the spaces are removed.

Then I again do the text to columns, delete all of the information I don't need, and it's done.

What I'm having trouble figuring out is how to do the "Find all" function and by extension the "Replace all" function, through VBA. Is there a way to do this?

I greatly appreciate any and all help, and hope everyone has a great weekend.

**TMS** · 05-13-2011, 03:54 PM

Record a macro while you do a replace all on your spreadsheet. That will give you the basis for your code. You'll then need to tweak it to generalise it.

I suggest that you post a sample workbook when you have some code you need help with. It can be difficult inventing sample data, workbook/worksheet structure and then developing code ... and it's unlikely to match what you have and need.

Regards

**realniceguy5000** · 05-13-2011, 04:19 PM

Hi,

You can play around with this script below,Post back additional Questions and as TM said post a dummy workbook that always helps.

Please Login or Register  to view this content.

.

**just_some_guy** · 05-16-2011, 11:05 AM

TM,

Thanks very much for the tip. When the recorder didn't put in any code for the "Find" function, I for some reason just assumed it would not do it for the "Replace" function either. Stupid assumption.

RealNiceGuy,

Thanks for the code. After a bit of tweeking, it does exactly what I need it to. I'm still running into trouble getting the "Find All" function to work in VBA. Any tips for that, from either of you?

Thanks again for your help. It is very much appreciated.

**TMS** · 05-16-2011, 11:44 AM

Some basic, untweaked code for Replace All and for Find (apart from making it more readable):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards

**just_some_guy** · 05-16-2011, 11:52 AM

TM, you're just about my favorite person in the world right now for the "Find" macro. I had to tweek it just a bit to fit my needs, but it is absolutely perfect.

Thanks a lot for all your help!