Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

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:

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

Open in new window


The Config Sheet should be set up like this:

User generated imageI 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
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Hello experts,

Here is my proposal:

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
    
'==> Loop data reported in Column C of Config-R
For Each rng In wksConfig.Range(wksConfig.Range("C2"), wksConfig.Range("C2").End(xlDown))
bExists = False
    '==> Set up Destination Sheet
    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 one Destination Sheet doesn't exist stop the program
        If Not bExists Then
        MsgBox "Worksheet: '" & rng.Offset(0, 1).Value & "' is not valid"
        Exit Sub
        End If
        
    '==> i equal to 0 to perform the count and report it then
    i = 0
   '==> If value reported in column E is equal to 1 make a whole replacement
   '==> ?? Please advice if the for Each loop is the the best way in term of performance ??
   If rng.Offset(0, 2).Value = 1 Then
        MsgBox ("Flag is equal is different to 0")
        For Each c In wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp))
            If InStr(1, c, rng.Offset(0, -2).Value, 1) Then
            c.Value = rng.Offset(0, -1).Value
            i = i + 1
            End If
        Next c
    i2 = 0
    '==> i2 equal to 0 to perform the count and report it then
    '==> If value reported in column E is equal to 1 make a whole replacement
    ElseIf rng.Offset(0, 2).Value = 0 Then
        MsgBox ("Flag is equal is different to 1")
        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
       '==> ?? i2 = i2 +1 doesn't work how can I perform the count of replacement in this condition ??
    Else
    MsgBox ("Range" & rng.Offset(0, 2).Value & "is not valid")
    End If
    
    '==> Update Replacement count
    If rng.Offset(0, 2).Value = 1 Then
    rng.Offset(0, 3).Value = i
    ElseIf rng.Offset(0, 2).Value = 0 Then
    rng.Offset(0, 3).Value = i2
    End If
Next
    Application.ScreenUpdating = True
End Sub

Open in new window


Can someone advice related to the commented lines which have a ?? question mark:

Section1:
 '==> ?? Please advice if the for Each loop is the the best way in term of performance or is there another best way??
   If rng.Offset(0, 2).Value = 1 Then
        MsgBox ("Flag is equal is different to 0")
        For Each c In wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp))
            If InStr(1, c, rng.Offset(0, -2).Value, 1) Then
            c.Value = rng.Offset(0, -1).Value
            i = i + 1
            End If
        Next c

Open in new window

i2 = 0
    '==> i2 equal to 0 to perform the count and report it then
    '==> If value reported in column E is equal to 1 make a whole replacement
    ElseIf rng.Offset(0, 2).Value = 0 Then
        MsgBox ("Flag is equal is different to 1")
        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
       '==> ?? i2 = i2 +1 doesn't work how can I perform the count of replacement in this condition ??

Open in new window



Thank you very much for your help.
Avatar of Ryan Chong
just did a quick test for your requirement:


-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.
which you can use codes like this:
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)

Open in new window

for your other requirements, I will have to look deeper to see whether there are quick solutions
for requirement:

-Add a check in Column E if the value reported is different from 0 or 1.

you can simply add a data validation rule

User generated image
for requirement:
-Report in column F the number of lines which have been replaced
try 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 & "*"))

Open in new window

so complete amended file is as attached.
Replace-values-based-on-Config_b.xlsm
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.

User generated image
The value above should be replaced by 45 as it contains string contains (like) toto. but the value is not replaced
User generated image

Thank you very much for your help.
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
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial