Find and Replace Macro- Within Workbook or Sheet

**Rhudi** · 03-13-2013, 05:30 PM

If I do a Find (or replace), under 'Options' is 'Within'...
I can manually pick 'sheet' or 'Workbook', but that will be a sticky change.
I have a macro that needs to do a 'sheet-only' replace.
But, if I did a 'Workbook' search earlier, that macro does some unpleasant things.
I have several sheets with source data, that gets copied to a target sheet. within that sheet (during the copy) I need to replace a value.
If I picked 'Workbook' before, the source changes too (BAD).
How can I make the macro force a sheet-only replace?

**AlphaFrog** · 03-13-2013, 06:27 PM

This is a bug (or oversight) with the Replace method. It's not an issue with the Find method.

With the Find method, if you specify a specific worksheet and range to search, it will not search the entire workbook regardless of the previous WithIn setting. Unfortunately, the Replace method doesn't respect the same usage.

The work-around for the Replace method is to first do a dummy Find to reset the WithIn setting to search just the worksheet.

Example: do this before your Replace.

Please Login or Register  to view this content.

**Rhudi** · 03-14-2013, 09:19 AM

bugs like this keep us awake and alert. Well, that or caffeine dependant.

**AlphaFrog** · 03-14-2013, 12:26 PM

You're welcome.

**Rhudi** · 03-14-2013, 12:53 PM

...and thank you (sorry, I wasn't awake earlier)

Find and Replace Macro- Within Workbook or Sheet

LinkBack

Thread Tools

Rate This Thread

Display

Find and Replace Macro- Within Workbook or Sheet

Re: Find and Replace Macro- Within Workbook or Sheet

Re: Find and Replace Macro- Within Workbook or Sheet

Re: Find and Replace Macro- Within Workbook or Sheet

Re: Find and Replace Macro- Within Workbook or Sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions