REPLACE outside of highlighted column

[Jane]

I highlighted a column that contained a simple formula in a very large
spreadsheet. I used REPLACE function to replace the cell locations in the
formula so that I didn't have to re-type the formula with the new cell
locations.

I wanted to change the formula from =SUM(S6:S20). I wanted to change the
formula to =SUM(C6:C20). I used REPLACE to do this before.

THIS time, the REPLACE function changed cells - formulas and text - outside
of my highlight resulting in ALL of the S's on the spreadsheet to a C. ex.
the word submit was changed to "cubmit" and any formulas with a S cell
location changed to a C cell location.

any thoughts?

thank you in advance! jane

 

[Vasant Nanavati]

I could not replicate your results.

If, somehow, you neglected to highlight a range and had just one cell
selected, Excel would then have carried out the replacement on the entire
worksheet. You have to have at least two cells selected for the replacement
to be restricted to the selection.

 

[Biff]

Hi!

Why not just reverse the Find/Replace operation: Find "C" replace with "S".

Messing with the registry and changing undo levels costs memory!

Another possible option: Close the file without saving

Biff

 

[Alok]

Some Cs which were originally there will also change to S and hence this will
not work.

Alok Joshi

 

[Biff]

Hi!

It's my understanding that this is what happened to begin with.

The OP wanted to replace some "S's" with "C's" and *ALL* of them were
changed. Why not just reverse that to get things back to where they were?

Biff

 

[Alok]

Say a formula (=C3+C4) was already present before this across the board
change from Ss to Cs took place. When you change Cs to Ss this formula will
also change to =S3+S4.

Alok Joshi

 

[Biff]

Ok, I see what you mean.

Biff

Say a formula (=C3+C4) was already present before this across the board
change from Ss to Cs took place. When you change Cs to Ss this formula
will
also change to =S3+S4.

Alok Joshi

 

[bj]

since this was a large data base, Hopefully you did not save it after the
replace problem happened so that you did not lose too much. and could restart
from an older file.

I would watch this file. Unless you did one of the things discussed in the
other responses, such as only having one cell selected, it may indicate the
the file is getting corrupted.

I have had large active files get corrupted, and once they start they seldom
just have one problem. I use this as an excuse to regenerate from scratch,
when possible, my important files every so often (of course improving them
when I can.)

 

[Dave Peterson]

My bet is that you're running xl2002+.

You selected your range and did:
Edit|Replace (or even Find)
Then you clicked on Find All

That showed you all the finds in that dialog--but it also changed your selection
(that initial column/range is no longer selected).

Then instead of reselecting your range, you hit Change All and kablewie
(ka-bloo-y!).

So avoid that intermediate FindAll or reselect the original range.