VBA to replace multiple strings.

[kizmet311]

I have an excel file with several strings of text that I need to replace. The "Find" strings are all in the same column. So I need find in column C and replace with value's stored in the VBA, back into column C.

Find	Replace
ASSISTANT COACH - CLASSIFIED	Other
ASSISTANT COACH - PARA	Other
ASSISTANT COACH-XDUTY	Other
BUS AIDE - EXCEPTIONAL CHILD	Transportation Staff
BUS DRIVER TRAINING ASSISTANT	Transportation Staff
BUS DRIVER TRNING COORDINATOR	Transportation Staff
SUB BUS DRIVER	Transportation Staff
BUS MONITOR	Transportation Staff
CERTIFIED SOCIAL WORKER	Teacher/Professional
CERTIFIED STAFF	Teacher/Professional
CHIEF INFORMATION OFFICER	Technology Staff
CHILD DEVELOP CENTER ASST SUPR	Other
CHILD DEVELOPMENT CENTER SUPRV	Other
COMPUTER ENGINEER	Technology Staff
COMPUTER LAB TECHNICIAN	Technology Staff
COOK/BAKER	Cafeteria Staff
COORDINATOR I	Other
CUSTODIAL SUPERVISOR	Facilities Staff
SUB CUSTODIAN	Facilities Staff

 

[JLGWhiz]

Assuming you have a sheet named "Find-Replace" with the strings to find in column A and the replacement string in column B then you can use this code when the sheet with text to be replaced is the ActiveSheet.

Sub t()
Dim c As Range, sh As Worksheet
Set sh = ActiveSheet
    With Sheets("Find-Replace")
        For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
            Columns(3).Replace c.Value, c.Offset(, 1).Value
        Next
    End With
End Sub