This past week at school, I ran into a problem. I had a database of student information: Names, ID Numbers, etc. The only problem is that our barcodes used the wrong Student ID numbers so when we scan the barcodes, the wrong numbers pop up and we can’t search for students by barcode. For some reason each student has at least three different numbers to identify them: a State ID, Student ID, and Meal Program ID. This was the craziness.
Find and Replace by Columns
My simple solution was that I needed a way to do a Bulk Find and Replace by Columns. In other words, look for the value of Column F in Column A of the spreadsheet and wherever you find that value, replace it with Column G.
Sounds simple enough right?
My initial Googling didn’t turn up anything because I was searching for something like “Excel Find and Replace by Columns”. After about an hour with no luck and clicking through to a lot of dead ends, I changed my search. “Excel Bulk Find and Replace” And the second result on that page was exactly what I was looking for!
Here’s a link to that original article.
Here’s the script that worked for me.
Dim myList, myRange
Set myList = Sheets("sheet1").Range("D1:E11") 'confirm the sheet name then use two column range with find/replace pairs
Set myRange = Sheets("sheet1").Range("B1:B99") 'range to be searched and replace
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
Be careful when pasting code as the first time I copied and pasted this code, the quote marks were upgraded which rendered errors in the script.
I hope this code helps someone else who needs to do a bulk find and replace by columns.