Sunday, 7 September 2014

Find and Select / Replace with Multiple Fields

Excel's built-in Find / Find and Replace dialog box is quite useful. Unfortunately you can only find/replace one thing at a time.

(This doesn't mean you can't select all cells with the same value at the same time. Just use the Find All button, select one of the results and click Ctrl and A on your keyboard at the same time to select all cells with the same value. The thing is you can only "find" one thing at a time).

Anyway, recently I've had to find/replace in lots of cells in various worksheets, and doing the same thing over and over again led me to say "Oh dear, what a bother!". (Well, perhaps I used stronger language than that)

So I made this.



Notice the Add Field button? Click it.



Now you can add as many fields as you like :-)

How to use (Find and Select)
Well that should be fairly obvious. Type in any values to want to find. Choose from the options - Selected Range or Active Sheet, Part or Whole, Formulas or Values.

"Part" means partial so "an" will be found in "Anthropology" and "Cyan". Whole will only find the the whole field (word, phrase, etc)

"Formulas" will find within formulas, and also values. If you want to find "3", it won't be found in "=1+2". It will be found in cells that contain "3". Using the "Partial" option this would also apply to cells with "31", "123" or "=A1+A3".

"Values" will find within cells that show "3" regardless if a formula to calculate the value is used or not. So, with the "Partial" option selected "=1+2", "31", "123" will be found. "=A1+A3" won't be found if the formula does not return a "3".

Whew!

And the last part - clicking the "Find and Select" button will do exactly that. All cells that meet all criteria will all be selected at the same time. You can do whatever you want to them thereafter, such as change their colour, etc, etc.

Even though the code this is quite fast, please be sensible and keep in mind that the smaller the range, the faster it will work. If it's taking too much time, click "Esc" on your keyboard to quit. Keep in mind it should be much faster, and less frustrating, than using the built-in "Find" dialog box if your working with multiple fields.

How to use (Find and Replace)
Basically "Find and Replace" works the same way.



Cells won't be selected, but they will be replaced. Note that "Formulas" and "Values" option buttons disappear. Just the like the built-in Find and Replace, the functionality is limited to formulas (refer to above).

Here's the download link. (It should work with Excel 2003 upwards)

A word of caution. Unlike "Find and Select", "Find and Replace" can't be exited by clicking the Esc key. And there's no Undo function. So be careful. I suggest you save your work before using it, and some practice and confirmation it's doing what you want won't hurt either. That said, it can be very useful and save you a lot of time.

There are more addins on the way. And I may or may not add more functionality to this addin in the future.

See you next time.

10 comments :

  1. Excellent idea. Worked perfectly and it replaces everything. Thanks for sharing.

    ReplyDelete
    Replies
    1. I'm using it right now at work :-)

      Delete
  2. When I first installed you addin, I thought it would be a while before I needed to use it and that I would forget I had it. I ended up using it the same day and I love it! Thank you for sharing :)

    ReplyDelete
    Replies
    1. Glad to hear you like it. I'm using it almost every day recently ;-)

      Delete
  3. I'm not a fan of interfering in the user's registry.
    So I made an alternative using an invisible 'Name' instead of 'savesetting' & 'getsettings'.
    You may find it at http://www.snb-vba.eu/bestanden/AET_alternative.xlsb

    ReplyDelete
  4. Hi Andrew. Great looking app, and I'll probably mention it in my upcoming book Excel for Superheroes and Evil Geniuses.

    I've got an enhancement suggestion: Allow users to select a list of terms in a range. I.e. a table with the headers "Find" and "Replace". Saves them from having to type terms individually in the dialog box, and lets them save common terms for reuse.

    Great looking app, and I'll probably mention it in my upcoming book Excel for Superheroes and Evil Geniuses.

    ReplyDelete
  5. Thanks Jeff. Using a range does seem like a good idea. I'll have a think.

    Good luck with the book. If you can send a copy, I'd like to review it.

    ReplyDelete
  6. "Apart from the mention of my utility, this book needs work". :-)

    Sure thing.

    ReplyDelete