How to Replace Header Values in Excel VBA Using a Control List
Question details
The user wants to write an Excel VBA macro to search a specific named range and automatically replace misspelled header values using a master list of corrections.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating the correction of inconsistent or misspelled column headers by mapping them to correct values stored in a separate control worksheet.
- Observed behavior
- The user needs a script that iterates through pairs of old and new values on a 'Control' sheet and applies the updates directly to a named range called 'Headers'.
Ensure your workbook contains a defined named range called 'Headers' and a separate worksheet named 'Control' where column A lists the misspelled values and column B lists the correct values.
Use a VBA Loop with Range.Replace
Iterate through the correction list on the Control sheet and apply the Range.Replace method to update the Headers named range automatically.
This method involves reading the old and new text values from your Control worksheet line by line. For each pair, the macro applies the replacement operation to the designated header cells.
You can customize the replacement behavior to match the entire cell content strictly or just substrings within the cell by adjusting the LookAt parameter.
Press ALT + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window.
In the top menu, click on 'Insert' and select 'Module' to create a blank script window.
Write a VBA 'For' or 'Do While' loop that references the 'Control' worksheet, pulling the target value from column A and the replacement value from column B.
Inside your loop, execute the replacement on your named range using the syntax: Range("Headers").Replace What:=oldValue, Replacement:=newValue, LookAt:=xlWhole.
Click the 'Run Sub/UserForm' button (the green play icon) or press F5 to execute the script and update your headers.
Run VBA Macros to Replace Headers in WPS Spreadsheet
WPS Spreadsheet provides powerful support for VBA macros, allowing you to run your header-replacement scripts seamlessly. Edit and execute your VBA code within a familiar, highly compatible interface.
- 1. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm workbook containing the headers and control list.
- 2. Access the VBA Editor: Navigate to the 'Tools' tab on the top ribbon and click on 'Macro' to open the VBA Editor.
- 3. Insert the Replacement Code: Right-click your workbook in the Project Explorer, choose 'Insert' > 'Module', and paste your Range.Replace loop code.
- 4. Execute the Macro: Press F5 or click the Run button to automatically process the control list and update your designated header cells.

Frequently Asked Questions
Why is Range.Replace changing partial words instead of the whole header?
If the LookAt parameter is omitted or set to xlPart, Excel defaults to replacing substrings. You must explicitly define LookAt:=xlWhole in your VBA code to ensure only exact cell matches are replaced.
How do I correctly define the 'Headers' named range?
Highlight the row or specific cells containing your data headers, click into the Name Box located to the left of the formula bar, type the word 'Headers', and press Enter.
Can this VBA macro target a specific worksheet if I have multiple named ranges?
Yes. If the named range is scoped locally to a specific sheet, you should qualify the range in your code by referencing the worksheet first, for example: Worksheets("DataSheet").Range("Headers").Replace...




