Find all/Select All/Replace all

[just_some_guy]

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.