Find and Replace in multiple sheets

**Josh_123456** · 06-11-2011, 01:45 PM

I have 7 sheets where I want to find a value in Column A and replace with another value in each sheet

I believe that I need an array of some sort, to which I am not sure how.

For simplicity:

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6
Sheet7

InputBox for Find value
InputBox for Replace value

Appreciate any help or feedback.

Josh

**MarvinP** · 06-11-2011, 02:04 PM

Hi Josh,

In the Find and Replace dialog there is a button called Options >>. Click on this and look at the "Within" dropdown. Pick Workbook instead of Sheet.

Does this do what you need? The only reason it wouldn't is if you can only search and replace in Column A of all sheets.

In the Find and Replace dialog you could also click on Find All. Then you can sort this list by the Cell it was found. You could then go down and only replace if it was found in a cell address that started with $A.

hope this helps. We need a little more info to give a better answer.

**Josh_123456** · 06-11-2011, 04:57 PM

I need to be able to do it thru code.

Right now when a new customer is entered into the system, the form increments the unique ID by one number and all the ID's are 11 digits.

When the company provides a tax id, then an actual account is setup, at this time, I would need to search all 7 sheets to find the temporary account number that was setup and replace with the new one.

So

1.) Have an input box ask what the temporary account number is (this i can do)
2.) Example (00000000012)
3.) Then go to all 7 sheets (Column A), find (00000000012) and replace with
4.) Input box for the new account number (e.g.... 12345678912)

Because this is a unique ID, it is only going to be on each sheet once.

Hope this helps, please let me know if clarification is still needed.

Thanks,

Josh

**MarvinP** · 06-11-2011, 05:29 PM

Hi Josh,

Record a macro that Searches and Replaces All using Workbook instead of Sheet. Use the value of your input box(s) for the Search and Replace terms and hang the code on a Button.

See the attached for proof.