find and replace

Multiple Find and Replace VBA

Recoding Reference Account Codes

Recently I was using a workbook that required some references re-naming, like doing a find and replace multiple times. I needed to replace part of each string in the list with the characters ‘dept’ and each change had to be applied throughout the workbook. I suppose it was convenient that the characters to replace were always characters 5-8 in the strings (but it is not hard to write an algorithm to replace on other conditions).

My list of reference strings to update

I needed to use multiple find and replace VBA to convert every instance of these strings to the relative pattern ‘????dept*’

multiple find and replace vba: I needed to convert every instance of these strings to the relative pattern '????dept*'
my account codes

I initially thought I would just do some find and replaces manually but realized this was going to be time consuming. So I wrote the routine below to perform the find and replace work for me. I have tried to comment each line so you can follow the code more easily. The attached solution workbook contains my list of account codes in Sheet1 as well as code that performs the multiple find and replace work. Just download this workbook, open, hit Alt+F11 then step through the code by pressing F8 to see the routine executing.

Multiple Find and Replace VBA Technique (Excel)

Sub f_and_r() 'routine for making a series of workbook find+replace changes

'place cursor here and press F8 repeatedly to step through the code, open excel sheet in a seperate window to see changes being made as you execute each line

Dim i, j, k As Integer
Dim sht As Worksheet
Dim bk As Workbook
Dim what1, replace1 As String

Set bk = ThisWorkbook
Set sht = ThisWorkbook.Sheets("Sheet1")
For i = 4 To 12 'rows to loop through
 what1 = sht.Range("a" & i) 'the string to find
 replace1 = Left(Range("a" & i), 4) & "dept" & Mid(Range("a" & i), 9, 10) 'the replacement for the find string
 Debug.Print what1, replace1 'just a quick reference check to make sure what and if are correct--this line prints the text to find and the text to replace to the immediate window below (Ctrl+G for shortcut to immediate window)
 Cells.Replace what1, replace1 'executing the replace method on the whole workbook
Next i

End Sub

Share your thoughts below :)