Find and string in a string and change it-CodePudding

I have a 2-dimensional array of values looking like that:

In a different table, I have long strings with VALUE_1, VALUE_2 that can be found anywhere. It looks like in the table below:

Now, I want to write a program that translates the existing VALUE_1, VALUE_2 etc. in the long strings by adding the respective element in the 2nd dimension of the array (/BB, /CCC etc.) and if necessary duplicating and separating the values with a comma and a blank space. So VALUE_1 for example is turned into VALUE_1/BB, VALUE_1/A for each finding in the string. The result is supposed to look exactly like in the table below.

That's challenging. I my first approach I tried to locate the VALUE_1, VALUE_2 in the strings by using InStr() but I don't think that this will help me since only the first hit is taken into consideration. I need every occurrence.

For i = 1 To Worksheets("table2").Range("H1").End(xlDown).Row

    For j = LBound(arr2) To UBound(arr2)

        If InStr(Worksheets("table2").Range("H" & i), arr2(j, 0)) > 0 Then

            Worksheets("table2").Range("H" & i).Font.Bold = True

        End If

    Next j

Next i

CodePudding user response：

Use your 2D table to build a scripting dictionary so that value1 is associated with the concatenation of all column values in column 2 that have value 1 in the first column.

In the (untested) code below the array (ipArray)is that derived from the 2D range.

Public Function GetReplacements(ByVal ipArray As Variant) As Scripting.dictionary

    Dim myD As Scripting.dictionary
    Set myD = New Scripting.dictionary
    
    Dim myIndex As Long
    For myIndex = LBound(ipArray) To UBound(ipArray)
        Dim myKey As String
        myKey = ipArray(myIndex, 1)
        
        Dim myItem As String
        myItem = ipArray(myIndex, 2)
        
        If myD.exists(myKey) Then
        
            myD.Item(myKey) = myD.Item(myKey) & ", " & myKey & myItem
            
            
        Else
        
            myD.Add myKey, myKey & myItem
        
        
        End If
        
    Next
    
    Set GetReplacements = myD

End Function

Now when you find an item such as "Value 1" you can replace with the value retrieved from the dictionary.

CodePudding user response：

Building on @freeflow's excellent answer, I would also use a Scripting.Dictionary to hold the mappings from VALUE1 etc. to the target text.

I would then use Replace for each key in the Dictionary. You can loop like:

Dim key as Variant
For Each key in dict
    Replace(<your string>, CStr(key), dict(key))
Next key

This will work so long as all your 'find' strings are totally unique i.e. none of them appears within another - so if you had "Value" and "Value 1" it would not work. Also, the simplest form of this method only works if there is a one-to-one mapping of text strings. Thus, if your sample data is representative, you would want to look into using the Count argument of Replace so that you can replace the second occurrence of VALUE_4 with the different text, and so on.

I would do this by storing the dict values as an array e.g.

Dim my_arr(1 to 3) as String
my_arr(1) = "VALUE_4/CCC"
my_arr(2) = "VALUE_4/DDDD"
my_arr(3) = "VALUE_4/A"
dict.Add "VALUE_4", my_arr

Then when you are looping through, you can keep track of a counter (call it 'i' for example) and then you can just use Replace with a count of 1, increment 'i' by 1, and then use 'i' in each iteration to call on the relevant element of the array stored against VALUE_4 in the dict, like:

For Each key in dict
    For i = LBound(dict(key)) to UBound(dict(key))
        Replace (<your string>, CStr(key), dict(key)(i), 1, 1)
    Next i
Next key

Hopefully you can build from there to what you need? Having reread your original post, I actually think my simplest solution would work (but I'll leave the more complex solution there in case it's of use to you or others), so long as dict is used to store the one-to-one mapping of, for example, "VALUE_1" to "VALUE_1/BB, VALUE_1/A" - you can loop through your original table and build those strings by concatenation - maybe even directly in the dict:

For Each cell in TableCol1 ' assuming it is cells, otherwise use an appropriate form of loop
    tmp_str = cell.Value2
    If dict.Exists(tmp_str) Then
        dict(tmp_str) = dict(tmp_str)   ", "   tmp_str cell.Offset(0,1).Value2
    Else
        dict.Add tmp_str, tmp_str   cell.Offset(0,1).Value2
    End If
Next cell