There is a dangerous little trap in using Find & Select dialog on the Home Tab and then using Replace All.
I use this a lot and most of the time need to change lots of cells so in the habit of using Replace All. I am often changing the sheet name or a range name in formulas. Perhaps you want to edit text through out the worksheet because the client is sensitive to the word Bonus and wants it changed to Productivity Payment. Be careful when choosing just 1 cell and use Replace All. Excel changes ALL formulas on the worksheet if you have selected just one cell. This could be catastrophic if you don’t notice this and save. If you select a range of at least 2 cells then it works as I would have expected, on just those 2 cells. If you remember to instead just use Replace with 1 cell selected then there is no problem. This seems like a bug to me as of course Excel knows 1 cell is a range so logically I would have thought Replace All should be restricted to that 1 cell range if that is all selected. If we wanted a Replace All on the entire worksheet which is a big deal I would have thought this should be a clearer, separate choice. The danger is, you might do this, not notice the confirming message of the number of cells changed then work away for hours or days saving over the rubbish your Replace All has created.
To illustrate… I have a nothing formula in these 16 x cells of $A$1* B1*3. So then say for some reason I only want to change A1 replacing A1 with “GST” which I intend to set up later as a rangename with a 10% percentage value. Until I set this rangename up an error shows which illustrates my point better below. If I select A2 and go ahead and do my Replace All you can see it does 16 replacements or the whole worksheet of formulas – not what I want. If I notice the obvious error in this, great, I can undo then select a range of A2:B2 and then the Replace All just does those 2 cells. Not want I wanted but easier to quickly fix just B2. The best way however is to select A2 and just use Replace and then only the 1 cell will be replaced.
If you are a nerd like me read on as I was curious as to what Excel was thinking here. I recorded a macro under the 3 scenarios and here is the code.
[vb]
Sub 1_ReplaceAllWith1Cell()
Cells.Replace What:=”A”, Replacement:=”GST”, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
Sub 2_ReplaceAllwithRange()
Range(“A2:B2″).Select
Selection.Replace What:=”A”, Replacement:=”GST”, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Sub 3_Replaceonly1Cell()
Range(“A2″).Select
ActiveCell.Replace What:=”A”, Replacement:=”GST”, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:=”A”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
[/vb]
- Using Replace All with 1 cell Excel uses the object Cells which is the entire sheet so obviously why in this scenario all formulas on the sheet will be changed.
- Using Replace All with a range – Excel uses the object Selection which represents a range so why only the selected range only is affected.
- Using Replace with 1 cell selected Excel uses the object ActiveCell which can only be 1 cell so why you can’t go wrong using this.
So OK I feel better now and will have a better awareness of this issue as I can see in the VBA code what Excel is thinking.