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:

2015-09-20-22_31_35-Microsoft-Excel-.pngI 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
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LD16Author Commented:
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.
0
Ryan ChongCommented:
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
0
Ryan ChongCommented:
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

add data validation rule
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
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

0
Ryan ChongCommented:
so complete amended file is as attached.
Replace-values-based-on-Config_b.xlsm
0
LD16Author Commented:
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.

2015-09-24-17_02_47-Microsoft-Excel-.png
The value above should be replaced by 45 as it contains string contains (like) toto. but the value is not replaced
2015-09-24-17_05_17-Microsoft-Excel-.png

Thank you very much for your help.
0
LD16Author Commented:
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
0
Ryan ChongCommented:
ok, if I understand correctly... try this:

Sub Replacement()
    Dim wksConfig As Worksheet
    Dim wks As Worksheet, ws1 As Worksheet
    Dim rng As Range
    Dim c 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
        If rng.Offset(0, 2).Value = 1 Then
            cnt = 0
            Set c = wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp)).Find("*" & rng.Offset(0, -2).Value & "*")
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    c.Value = rng.Offset(0, -1).Value
                    cnt = cnt + 1
                    Set c = wks.Range(wks.Range(rng.Value & "2"), wks.Range(rng.Value & wks.Rows.Count).End(xlUp)).FindNext(c)
                Loop While Not c Is Nothing
            End If
            rng.Offset(0, 3).Value = cnt
        Else
            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 & "*"))
            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, xlPart
        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, IIf(rng.Offset(0, 2).Value = 1, xlWhole, xlPart)
    Next
    Application.ScreenUpdating = True
End Sub

Open in new window

Replace-values-based-on-Config_c.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.