Excel - Regular Expression replacement with dictionary value

Question

We are trying to automatically translate certain part of a string in each Excel cell automatically based on an VBA dictionary.

Original String examples:

1.Outer Fabric:2% EA, 44% WO, 54% PES; Lining:4% EA, 96% RY
Outside:2% EA, 98% WO
1.Outer Fabric:27% PA, 73% WV; 2.Lining:100% CO; 2.Outer Fabric:100% AOS

Regular expression is defined as:

Dim strPattern As String: strPattern = "(\d{1,3}\%\s+)(\w+)"

The dictionary is constructed from another excel spreasheet. The example key/value pairs are:

EA: Leather
WO: Cloth
PES: Polyester
RY: Other
...

But I cannot find a way to use those dictionary keys to replace the original string. Line 12 below is what I tested, but it cannot find the dictionary value...

Dim strPattern As String: strPattern = "(\d{1,3}\%\s+)(\w+)"
Dim strInput As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A2:A50")
With regex
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = strPattern
End With
Dim strReplace As String: strReplace = "$1" & IIf(Dict.Exists("$2"), Dict("$2"), "$2")

For Each cell In Myrange
    If strPattern <> "" Then
        strInput = cell.Value
        cell.Value = regex.replace(strInput, strReplace)
    End If
Next

Any guidance to get this problem resolved is greatly appreciated. Thanks!

Answer 1

I don't think you need regex for this. When I translate I usually just use brute force with replace.

str = Replace ( str, "EA", "Leather")
str = Replace ( str, "WO", "Cloth")
str = Replace ( str, "PES", "Polyester")

Etc.
And once all replace has been done you know it has translated the abreviations that excist.
If WO is not in the string then the replace will fail and just continue with the next.

Answer 2

Here's a basic outline:

Sub Tester()

    Dim regEx As Object, dict As Object
    Dim matches, m
    Dim c As Range
    Dim s As String, mat As String

    Set dict = CreateObject("scripting.dictionary")
    dict.Add "EA", "Leather"
    dict.Add "WO", "Cloth"
    dict.Add "PES", "Polyester"
    dict.Add "RY", "Leather"

    Set regEx = CreateObject("vbscript.regexp")
    regEx.Pattern = "(\d{1,3}\%\s+)(\w+)"
    regEx.Global = True
    regEx.IgnoreCase = True
    regEx.MultiLine = True

    For Each c In ActiveSheet.Range("A1:A10")
        s = c.Value
        Set matches = regEx.Execute(s)
        If Not matches Is Nothing Then
            'loop over each of the match objects
            For Each m In matches
                mat = m.submatches(1) '<<second submatch=material code
                If dict.Exists(mat) Then
                    s = Replace(s, m, Replace(m, mat, dict(mat)))
                End If
            Next m
        End If
        c.Offset(0, 1).Value = s
    Next c

End Sub