Macro for multiple find and replace criteria

[ttbuson]

Hi All,

I have data that is one of six values in column H (ABC, AAA, CAB, BAC, BBB, CCC).

I would like to replace each of those values with a text value from another list. So, if a cell says ABC, I would like to replace it with the text ONE; if it says AAA, then replace it with TWO, and so on.

Is there a fast and easy way to set that up in a macro?

I had an idea of how to do it, but I think it's getting really complicated and I know there has to be an easy way of doing this.

Thanks in advance for your help!

 

[guerillaunit]

You can use a filter. In Excel 2002/2003, go to "Data" → "Filter" →"AutoFilter". For each value that you filter in column H, replace the data with the value you want

 

[bamblack]

If you want a macro to do this for you, you can use this. I'm still learning to use arrays but this should work nicely.

Sub Replace_Values()

Dim myVar() As String, myRepl() As String, currBook As String
Dim myCol As Range
Dim LastRow As Long

'Replace C:\Documents and Settings\bb185064\Desktop\Test1.xlsx with the pathname
'of the file that contains the list
currBook = ActiveWorkbook.Name
Workbooks.Open "C:\Documents and Settings\bb185064\Desktop\Test1.xlsx"
Max = Range("A" & Rows.Count).End(xlUp).Row - 1

ReDim myVar(1 To Max)
ReDim myRepl(1 To Max)

With Range("A1")
    For i = 1 To Max
        myVar(i) = .Offset(i, 0)
        myRepl(i) = .Offset(i, 1)
    Next i
End With

ActiveWorkbook.Close

Windows(currBook).Activate
LastRow = Range("H" & Rows.Count).End(xlUp).Row
Set myCol = Range("H2:H" & LastRow)

For i = 1 To Max
        myCol.Replace What:=myVar(i), Replacement:=myRepl(i), LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
Next i

End Sub

The only thing you would have to change it the Workbooks.Open line that has the comment above it. Also, this macro should be run when you have the book that the replacements will be made on as your activeworkbook.

All that you have to do to maintain this is keep that list up to date. If you choose to use this then you should have a workbook set up with what you want to replace in column A starting at A2 and what it should be replaced with in column B.