asked on

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.

Ejgil Hedegaard

Use the replace function like this

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

Open in new window

Luis Diaz

ASKER

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

Luis Diaz

ASKER

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

Ejgil Hedegaard

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.

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

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

Luis Diaz

ASKER

Sorry I haven't see your last message before posting my message.