Take Care with Replace All
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.
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
- 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.