Trying to replace a value in Excel with code but it ends up like the below.
| GREENHOUS FLEET SERVICES FLEET SERVICES FLEET SERVICES |
The code is here
.Replace what:="GREENHOUS", replacement:="GREENHOUS FLEET SERVICES"
How could I alter it so it does not repeat itself?
I have about 50+ of these in my code how can we apply the code to 50+replacments
Make a List in Excel and the code can loop through it
Column A - text to replace
Column B - text to replace with
Create your list then run this code. The list should be on a separate sheet, in the code it is Sheet1 but you can change that.
The sheet to change should be active.
Option Explicit
Sub FindReplace()
Dim rCl As Range, rRng As Range
Set rRng = Sheet1.Range("A1").CurrentRegion.Columns(1)
For Each rCl In rRng.Cells
ActiveSheet.Cells.Replace What:=rCl.Value, Replacement:=rCl.Offset(, 1).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Next rCl
End SubI`ve done what you said but can`t work out how to add it to my code?
rcl Says out of con range
Attach your workbook
Don’t have an account yet? Register yourself now and be a part of our community!