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*’
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