VBA & Macro Problems

How to Replace Multiple Values from Another Worksheet using Excel VBA

Aamir Naveed Akram Oct 23, 2026 869 views

Question details

The user needs to automate a search-and-replace operation using Excel VBA, replacing multiple values in an Audit worksheet based on a predefined mapping list located in a Check worksheet.

How to Replace Multiple Values from Another Worksheet using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Executing a batch update in one worksheet by reading search-and-replace pairs dynamically from rows in another worksheet.
Observed behavior: Requires a VBA macro to systematically read mapping pairs and apply the updates to a specific column without manually replacing each value.

Before you start

Before running any VBA scripts, ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and create a backup copy of your file to prevent accidental data loss during the batch replacement.

Solution 1Recommended

Create a VBA Macro to Loop Through Mapping Pairs

Use a VBA loop to iterate through the mapping worksheet and apply the batch replacement to the target data column.

By utilizing a For loop in VBA, you can instruct Excel to read the search criteria from the first column of your mapping sheet (e.g., 'Check') and grab the replacement data from the adjacent column. It then applies these rules directly to the target range (e.g., column L on your 'Audit' sheet).

Access the VBA Editor

Open your workbook and press the Alt + F11 keys simultaneously to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click on 'Insert' in the top menu bar and select 'Module' to create a blank script window where you can write your macro.

Write the Replacement Logic

Define your variables for the worksheets and the loop. Create a loop that iterates through the rows of your 'Check' sheet. For each row, use the value in column A as the find parameter and column B as the replace parameter, and apply the Replace function to Columns("L") of your 'Audit' sheet.

Run the Macro

Press F5 or click the 'Run Sub/UserForm' button (the green play triangle) in the toolbar. The macro will process the mapping sheet and update all matching values in the target worksheet.

Create a VBA Macro to Loop Through Mapping Pairs

Adjusting Cell References: Ensure you update the worksheet names ('Check', 'Audit') and column letters in your VBA code to precisely match the structure of your actual workbook.

Advanced Macro Support

Run VBA Macros Seamlessly with WPS Office

WPS Spreadsheet provides built-in support for VBA macros, enabling you to automate complex tasks like multiple value replacements without altering your existing scripts.

1. Open your Macro Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the mapping and audit data.
2. Navigate to the Developer Tab: Click on the 'Developer' tab in the top ribbon to access the automation tools.
3. Access the VBA Editor: Click 'Visual Basic' or 'Macros' to open the code editor, where you can paste or review your search-and-replace loop script.
4. Execute the Task: Run the macro directly within WPS Spreadsheet to instantly update your target column based on the mapping pairs.

Fully compatible with Microsoft Excel macro-enabled formats (.xlsm, .xlsb).Automate repetitive find-and-replace tasks across multiple worksheets effortlessly.Familiar VBA editor interface ensures a zero-learning-curve transition.Lightweight installation with robust performance for heavy automation workloads.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not running in the workbook?

Macros may be disabled due to security settings. Navigate to the Developer tab, click on Macro Security, and enable macros. Additionally, verify that your file is saved in a macro-enabled format (.xlsm).

Can I target a different column for replacements in the Audit sheet?

Yes. In your VBA script, modify the range reference (for example, change Columns("L") to Columns("M")) to target the specific column where you want the replacements to occur.

How do I make the VBA Replace function case-sensitive?

You can enforce case sensitivity by adding the MatchCase:=True parameter to the Replace method inside your VBA loop.

Will the script stop if there are blank rows in the mapping worksheet?

Depending on how the loop is constructed, it might process empty values or stop early. It is best to ensure your mapping list is contiguous or include an If statement in your code to skip blank cells.

Excel & Data Problems