VBA excel: Function used for replace values dynamically

Hello Experts,

I use the following procedure to replace dynamically values



Sub ReplaceValues(wsName As String, Col1Number As Integer, strRangeOriginal As String, strRangeReplaced As String, strFlag As Integer)

    Dim Report As Worksheet
    Dim i As Long
    Dim lastRow As Long
    
    Set Report = Excel.Worksheets(wsName)

    lastRow = Report.UsedRange.Rows.Count
    
    Report.UsedRange.ClearFormats


    For i = 2 To lastRow
        If strFlag = 0 Then
            If Report.Cells(i, Col1Number).Value = strRangeOriginal Then
                Report.Cells(i, Col1Number) = strRangeReplaced
                lTrueNo = lTrueNo + 1
          End If
        ElseIf strFlag = 1 Then
            If Report.Cells(i, Col1Number).Value Like "*" & strRangeOriginal & "*" Then
                Report.Cells(i, Col1Number) = strRangeReplaced
                lTrueNo = lTrueNo + 1
            End If
        Else
           If Report.Cells(i, Col1Number).Value Like "*" & strRangeOriginal & "*" Then
                'to complete with the required condition replace just the string related to strRangeOriginal
                'Report.Cells(i, Col1Number).Value = totoFFFFFFFFFFFFFF strRangeOriginal = toto and strRangeReplaced = 24
                'the result should be 24FFFFFFFFFFFFFF
            End If
        End If
    Next i
    
End Sub

Open in new window


I Use the strFlag to apply the following replacement conditions:

strFlag=0 perform an exact replacement strRangeOriginal = tata and  strRangeReplaced=toto
then Report.Cells(i, Col1Number).Value  will become toto if it was equal to tata
strFlag=1 perform a like replacement example if strRangeOriginal=tatafjjfjfjfjfjfjfjfjfjfj and strRangeReplaced=toto
Report.Cells(i, Col1Number).Value will become toto if it like tata


Now I need to built an else condition if Report.Cells(i, Col1Number).Value  like stRangeOriginal then just replace the string part by strRangeReplaced:

Example
Report.Cells(i, Col1Number).Value = totoFFFFFFFFFFFFFF
strRangeOriginal = toto and
strRangeReplaced = 24
the result should be 24FFFFFFFFFFFFFF

Thank you very much for your help.
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Ejgil HedegaardCommented:
Use the replace function like this

Report.Cells(i, Col1Number).Value = Replace(Report.Cells(i, Col1Number).Value, strRangeOriginal, strRangeReplaced, vbTextCompare)

Open in new window

LD16Author Commented:
Thank you very much for your help.

I tested but I don't know why I don't get the required replacement.
Please find attached one example. Line 4 of my config sheet doesn't perform the replacement.
I putted in the Sheet concerned by the replacement in Column C how should be the replacement when strFlag is different from 0 or 1.

Thank you again for your help.
Replace-values-based-1.xlsm
LD16Author Commented:
Very strange if I use the function like this it works:

Report.Cells(i, Col1Number).Value = Replace(Report.Cells(i, Col1Number).Value, strRangeOriginal, strRangeReplaced, 1, 1)

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ejgil HedegaardCommented:
strFlag is an Integer value, and a blank cell equals 0.
Use another value (2, 3 etc.) than 0 or 1 as flag value.

You should never get to the else statement with the replace function with a blank cell as flag.
I tried the method you describe, it did not work either, because row 4 use the first if with 0.

The first 1 is the starting position for the replace, and default is 1, so that does not change anything.
The second 1 is the number of replacements, and here is only 1, so no change, set it to -1 (default value) to replace all matches in the string.
If vbTextCompare is not used it is a binary compare, where the letters must match exactly, because in VBA fff is not equal to FFF, but with text compare on it is.
Ejgil HedegaardCommented:
Try this, where you can use 0 and 1 as flag, and use blank cells as ELSE.
Flag value converted to string in the call ReplaceValues2, and then a blank cell is "", and thus different from 0.
Replace-values-based-1.xlsm

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
LD16Author Commented:
You are totally right it works your first proposal works I should put a control to restrict empty value for the strFlag.
Concerning the control when you want to just loop one column is the best way to perform a loop as reported bellow or is there another way

 For col = 5 To 5
        If wsConfig.Cells(rw, col) <> "" Then
            If Not WorksheetFunction.IsText(wsConfig.Cells(rw, col)) Then
             i = i + 1
             End If
        End If
Next col

I attached the last version of the file.
Replace-values-dynamically-2.xlsm
LD16Author Commented:
Sorry I haven't see your last message before posting my message.
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
Microsoft Excel

From novice to tech pro — start learning today.