I have the following code which allows me to replace values dynamically based on a Config Sheet:
Dim wksConfig As Worksheet
Dim wks As Worksheet, ws1 As Worksheet
Dim rng As Range
Dim bExists As Boolean
Set wksConfig = Worksheets("Config-R")
Application.ScreenUpdating = False
For Each rng In wksConfig.Range(wksConfig.Range("C2"), wksConfig.Range("C2").End(xlDown))
bExists = False
For Each ws1 In Worksheets
If ws1.Name = rng.Offset(0, 1).Value Then
bExists = True
Set wks = Worksheets(rng.Offset(0, 1).Value)
If Not bExists Then
MsgBox "Worksheet: '" & rng.Offset(0, 1).Value & "' is not valid"
wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp)).Replace rng.Offset(0, -2).Value, rng.Offset(0, -1).Value
Application.ScreenUpdating = True
The Config Sheet should be set up like this:
I would like to add some additional requirements:
-Take into account the value reported in Column E
If value is equal to 1 the replacement needs to be done as it is done, we replace the original value by the new one.
If value is equal to 0 we need to read the Original value as a like and replace the whole string by the New value reported in column B.
Example for the 0 case: I report my new value as LANC and my Original value as TOTO.
If I have values reported like this TOTOTITI, the new value should be like this LANC and not LANCTITI.
-Report in column F the number of lines which have been replaced
-Add a check in Column E if the value reported is different from 0 or 1.
Thank you very much for your help.