Search range and replace based on adjacent value in list

Question

Looking or a vba automated solution....

I have data that sometimes the headers are commonly misspelled with the same misspellings repeatedly. Evaluating the named range "headers", I need to search the terms in column A on sheet named "control", and replace with the terms in column B. This list may grow as I find more misspellings.

Answer 1

Run the following macro:

Sub CorrectHeaders()
Dim wsh As Worksheet
Dim r As Long
Dim m As Long
Dim rng As Range
Application.ScreenUpdating = False
Set rng = ActiveWorkbook.Names("Headers").RefersToRange
Set wsh = ActiveSheet ' or Worksheets("List") or ...
m = wsh.Range("A1").End(xlDown).Row
For r = 2 To m
rng.Replace What:=wsh.Range("A" & r).Value, Replacement:=wsh.Range("B" & r).Value, LookAt:=xlWhole
Next r
Application.ScreenUpdating = True
End Sub

This version will only replace entire cell contents. If you want to replace part of cell contents, change xlWhole to xlPart.

Answer 2

Thanks my friend!

When I make my first million.... I'm buying you a Krystal burger WITH cheese!

(....or White Castle Burger if you prefer. :-))