Luis Diaz
asked on
VBA Excel: Replace values dynamically based on a config sheet v2
Hello experts,
I have the following code which allows me to replace values dynamically based on a Config Sheet:
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.
Replace-values-based-on-Config-Shee.xlsm
I have the following code which allows me to replace values dynamically based on a Config Sheet:
Sub Replacement()
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)
Exit For
End If
Next
If Not bExists Then
MsgBox "Worksheet: '" & rng.Offset(0, 1).Value & "' is not valid"
Exit Sub
End If
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
Next
Application.ScreenUpdating = True
End Sub
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.
Replace-values-based-on-Config-Shee.xlsm
just did a quick test for your requirement:
which you can use codes like this:
-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.
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, IIf(rng.Offset(0, 2).Value = 1, xlWhole, xlPart)
for your other requirements, I will have to look deeper to see whether there are quick solutions
for requirement:
-Report in column F the number of lines which have been replacedtry this:
rng.Offset(0, 3).Value = Application.WorksheetFunction.CountIf(wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp)), IIf(rng.Offset(0, 2).Value = 1, rng.Offset(0, -2).Value, "*" & rng.Offset(0, -2).Value & "*"))
so complete amended file is as attached.
Replace-values-based-on-Config_b.xlsm
Replace-values-based-on-Config_b.xlsm
ASKER
Thank you for this solution.
I tested the code but I have a problem concerning the orginal Value reported in column A of Config-R:
toto with a flag equal to 1.
The value above should be replaced by 45 as it contains string contains (like) toto. but the value is not replaced
Thank you very much for your help.
I tested the code but I have a problem concerning the orginal Value reported in column A of Config-R:
toto with a flag equal to 1.
The value above should be replaced by 45 as it contains string contains (like) toto. but the value is not replaced
Thank you very much for your help.
ASKER
Just to clarify:
If flag is equal to 1 it search all the values in Column Letter Destination sheet which contains the Original value and replace the whole string by the Revised column
Ex: totott with flag=1 should be 45
If flag is equal to 1 it seach all the values in Column Letter Destination sheet which contains the Original value and replace just one part of the string by the revised Column
Ex: totott with flag= 0 should be 45tt
If flag is equal to 1 it search all the values in Column Letter Destination sheet which contains the Original value and replace the whole string by the Revised column
Ex: totott with flag=1 should be 45
If flag is equal to 1 it seach all the values in Column Letter Destination sheet which contains the Original value and replace just one part of the string by the revised Column
Ex: totott with flag= 0 should be 45tt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is my proposal:
Open in new window
Can someone advice related to the commented lines which have a ?? question mark:
Section1:
Open in new window
Open in new window
Thank you very much for your help.