MrExcel Publishing
Your One Stop for Excel Tips & Solutions

replacing multiple strings

Posted by kev on March 16, 2001 8:01 AM

Hey, I need to go through 10,000 entries in a spreadsheet doing a find and replace on about 100 strings, and would love to have a macro to handle this. I looked up an earlier post on the board and found something similar to this:

Sub Replace_name()
Dim RowNum As Integer
Dim LastRow As Integer

With Worksheets("Replace")
LastRow = .UsedRange.Rows.Count

For RowNum = LastRow To 1 Step -1
If Range("A" & RowNum) = " Mug" Then
Range("A" & RowNum) = ""
End If

Next RowNum
End With
End Sub

but am unsure how to modify it to my needs. 1) How do I modify to at least replace 1 string like the above? 2) Can I do multiple replaces by repeating the If-End If statement with different strings?

Thanks for your help.

Posted by Celia on March 16, 2001 5:14 PM

Add a blank worksheet("Sheet2"). Enter the strings you want to replace in Column A and the replacement strings in Column B. Activate the worksheet containing the 10,000 entries and try the following macro (make changes to the macro where indicated to fit your needs) :-

Sub Find_Replace()
Dim searchRng As Range
Dim ws As Worksheet, fnd As Range, rpl As Range, x%

Set searchRng = Range("A1:J1000") 'CHANGE TO FIT YOUR NEEDS
Set ws = Worksheets("Sheet2") 'CHANGE WORKSHEET NAME IF REQUIRED
Set fnd = ws.Range(ws.Range("A1"), ws.Range("A65536").End(xlUp))
Set rpl = fnd.Offset(0, 1)

For x = 1 To fnd.Cells.Count
searchRng.Replace What:=fnd(x), Replacement:=rpl(x), _
LookAt:=xlWhole 'CHANGE TO =xlPart IF REQUIRED
End Sub

Posted by kevin on March 17, 2001 7:27 AM

I eventually figured out the replacing part, but having it read from the list is definitely one better. Thanks very much!