VBA to replace parts of text strings according to a conversion table

[Hermac]

Hello,
I'm reasonably trained in VBA code but I can't get this one to work.
ToTrim is a long range wit too long strings that I want to shorten.
It contains 19 reappearing substrings (some with a * wildcard) that I want to cut out and replace by some much shorter string or nothing at all.
ReplacTab is a 2-columntable M1:N19 with the annoying substrings in the left column and the replacementes in the right. Looks like this :

So why does the code below give me the finger? No error messages either.
Thank you very much for any suggestion!

Sub Replacer()
Dim c As Range, ToTrim As Range, ReplacTab As Range
Set ToTrim = Sheets("Replac").[J2:J1000]
Set ReplacTab = ActiveWorkbook.Sheets("VasteData").[M1:N19]
For Each c In ReplacTab.Columns(1)
        ToTrim.Replace What:=c.Value, Replacement:=c.Offset(0, 1).Value, LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase _
        :=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Next c
End Sub

Gratefully,
Herman Van Noten

 

[Peter_SSs]

There would be a lot of manual typing to set up that sample data to test. Could we have a small sample of both sheets (including the expected results manually entered or explained) with XL2BB so the we do not have to do all that typing?

BTW, I also suggest ..

• that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• that you use the forum's code tags when posting code. My signature block below has more information. I have fixed that for you this time.

 

[steve the fish]

Think you need:

For Each c In ReplacTab.Columns(1).Cells

 

[Peter_SSs]

From an initial glance at your code it seems that your sheets and ranges are crossed over.

Shouldn't ToTrim be Sheets("VasteData").Range("J2:J1000")

.. and shouldn't ReplacTab be Sheets("Replac").Range("M1:N19")