Replace cleverly values in a cell with macros using regex?

Question

I've got the following macro :

Sub MacroClear()

Dim wbD As Workbook, _
    wbC As Workbook, _
    wsD As Worksheet, _
    wsC As Worksheet, _
    DicC() As Variant, _
    Dic() As String, _
    ValToReplace As String, _
    IsInDic As Boolean

Set wbD = Workbooks.Open("D:\Users\me\Documents\macro\Dictionnary", ReadOnly:=True)

Set wbC = Workbooks("FileToTreat.xlsm")

Set wsD = wbD.Worksheets("Feuil1")

Set wsC = wbC.Worksheets("draft")

ReDim DicC(1, 0)

For i = 1 To wsD.Range("C" & wsD.Rows.Count).End(xlUp).Row
    Dic = Split(wsD.Cells(i, 3), ";")
    ValToReplace = Trim(wsD.Cells(i, 2))
    For k = LBound(Dic) To UBound(Dic)
        IsInDic = False
        For l = LBound(DicC, 2) To UBound(DicC, 2)
            If LCase(DicC(1, l)) <> Trim(LCase(Dic(k))) Then
                'No match
            Else
                'Match
                IsInDic = True
                Exit For
            End If
        Next l
        If IsInDic Then
            'Don't add to DicC
        Else
            DicC(0, UBound(DicC, 2)) = Trim(Dic(k))
            DicC(1, UBound(DicC, 2)) = ValToReplace
            ReDim Preserve DicC(UBound(DicC, 1), UBound(DicC, 2) + 1)
        End If
    Next k
Next i

ReDim Preserve DicC(UBound(DicC, 1), UBound(DicC, 2) - 1)
wbD.Close
Erase Dic

    For l = LBound(DicC, 2) To UBound(DicC, 2)
        Cells.Replace What:="*" & Trim(DicC(0, l)) & "*", _
             Replacement:=Trim(DicC(1, l)), _
             LookAt:=xlPart, _
             SearchOrder:=xlByRows, _
             MatchCase:=False, _
             SearchFormat:=False, _
             ReplaceFormat:=False
    Next l


Erase DicC
Set wbD = Nothing
Set wbC = Nothing
Set wsD = Nothing
Set wsC = Nothing

End Sub

I'll try to explain : It takes from the dictionnary my "words to replace" (column C), all separated by a ";", and my "primary words" (column B).

image http://img11.hostingpics.net/pics/403257dictionnary.png

Then it searches in all the cells of my "file to treat" (via Cells.Replace), if it finds something in column C of my dictionnary, it replaces it with what's in column B.

But now that I've got "SCE" in my column C (For Sony Computer Entertainment, to be replaced by Sony in column B), even when SCE is in a word (for example : ascend), it replaces the word with Sony. I don't want to replace it if it's inside a word...

In Java, I'd have done it easily with p = Pattern.compile("[^a-zA-Z]"+keyword+"[^a-zA-Z]", Pattern.CASE_INSENSITIVE); but I have no idea how to solve this problem in VBA. I tried some things but it didn't work, had errors etc. so I came back to the start.

Take a look at this (in frequent VBA questions) : http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — R3uK, Jun 30 '15 at 14:01
@R3uK I already looked in this, but I don't understand how to apply it to my case, he talks about replacing a string if the regex matches, but in my case I have a string which always changes (All the expressions in my column C) surrounded with 2 patterns (which are `"[^a-zA-Z0-9_]"`). I searched in the "Loop through range" section but I don't understand what does the "strInput". — Malik, Jun 30 '15 at 14:29
Mkay, I never used regex but `strInput` is the value of the analyzed cell, it is just a variable to improve efficiency and don't go each time into the cells to read info. So instead of MsgBox, you could place your own instruction (replace there the value of the cell with the new value) — R3uK, Jun 30 '15 at 14:43
Try a live regex testing tool like [RegexR](http://www.regexr.com) which will let you try out your regex string without having to run a macro each time. Copy your test data to the text field and then try out search parameters in the query box at the top until the right elements are highlighted in the text field. — nicolaus-hee, Jun 30 '15 at 15:13
@R3uK Since I use `Cells.replace`, I don't know how to treat each cell one by one. I tried to use your "Updated version" in my previous question, but I have an error about using "For Each" in a Row object. — Malik, Jul 01 '15 at 08:34
@nhee I already have my pattern, I have to find `"\W" & keyword & "\W"` ^^ — Malik, Jul 01 '15 at 08:35

score 0 · Answer 1 · answered Jul 01 '15 at 12:12

So I changed few parameters in the replace method and proposed a loop for all your cells, you'll just have to set the right column (in second proposition : here B=2).

Parameters :

LookAt:=xlWhole 'To search for whole expression
SearchOrder:=xlByColumns 'Search in column
MatchCase:=True 'Will look for the expression with the same casing (not sure about this word...)

Try one of these :

For l = LBound(DicC, 2) To UBound(DicC, 2)
    Cells.Replace What:="*" & Trim(DicC(0, l)) & "*", _
         Replacement:=Trim(DicC(1, l)), _
         LookAt:=xlWhole, _
         SearchOrder:=xlByColumns, _
         MatchCase:=True, _
         SearchFormat:=False, _
         ReplaceFormat:=False
Next l

Or with the loop on each cell :

For l = LBound(DicC, 2) To UBound(DicC, 2)
    For k = 1 To wsC.Rows(wsC.Rows.Count).End(xlUp).Row
        wsC.Cells(i, 2).Replace What:="*" & Trim(DicC(0, l)) & "*", _
             Replacement:=Trim(DicC(1, l)), _
             LookAt:=xlWhole, _
             SearchOrder:=xlByColumns, _
             MatchCase:=True, _
             SearchFormat:=False, _
             ReplaceFormat:=False
    Next k
Next l

Replace cleverly values in a cell with macros using regex?

1 Answers1