Text Functions‎ > ‎

Mass Replace

                                                                                            Sample File:  MassReplaceExample.xls
"I have 70,000 skus I would like to change their category numerical value into the text format equivalent. There are 2,000 categories example below:"

                Sheet - CATEGORIES

"The data looks like so:"
                 Sheet - DATA

"It needs to look like:"


Option Explicit

Sub MassSearchReplace()
'Jerry Beaucaire   4/26/2010
'Using a reference table, search/replace many values on 2nd sheet
LookAt:=xlWhole to match entire cells and not partial strings
Dim SrchRng As Range, Itm As Range

Set SrchRng = Sheets("Categories").Range("A:A").SpecialCells(xlCellTypeConstants)

With Sheets("Data")
    For Each Itm In SrchRng
        .Columns("B:B").Replace What:=Itm, Replacement:=Itm.Offset(0, 1), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
    Next Itm
End With

Set SrchRng = Nothing
End Sub


It's up to you to create the table in the correct order. If you insert a string that is later going to replaced again, you'll give yourself problems.

    Sheet - CATEGORIES
  A B
1 Mark Sam
2 Bob Doug
3 George Robert
4 Sam Phil

This table would replace Mark with Sam, but later all the Sam names get changed to Phil, which is wrong.  In this table, the Sam/Phil search at the bottom should be moved to the top of the table.

     Sheet - CATEGORIES

Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!