How to Use VBA to Find and Replace in Each Excel Row Based on Cell Values
Question details
Create a VBA macro that loops through each row where column A has data, searches the full row for the value in column B, and replaces matching values with the value in column C.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a worksheet with data in column A, search values in column B, and replacement values in column C. They need each row processed individually rather than replacing values only in one column.
- Observed behavior
- The desired result is that every row containing data in column A is scanned for the column B value and all matching entries in that row are updated with the column C value.
Open the workbook in WPS Spreadsheets and make sure macros are enabled. Confirm the exact worksheet name because the macro references it by name.
Run a Row-by-Row VBA Find and Replace Macro in WPS Spreadsheets
Use this solution when you need to search every cell in each row for the value in column B and replace it with column C, stopping at the last used row in column A.
Use the following macro: Sub FindAndReplaceInEachRow() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Worksheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 1 To lastRow If Len(ws.Cells(i, "B").Value) > 0 And Len(ws.Cells(i, "C").Value) > 0 Then ws.Rows(i).Replace What:=ws.Cells(i, "B").Value, Replacement:=ws.Cells(i, "C").Value, LookAt:=xlPart End If Next i End Sub
In WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer, right-click the workbook or worksheet, choose Insert, and then click Module to create a new code module.
Paste the macro code into the module and change "Sheet1" in ThisWorkbook.Worksheets("Sheet1") to the actual worksheet name.
Place the cursor inside the macro and press F5, or go to Developer > Macros, select FindAndReplaceInEachRow, and click Run.
Quickly Copy Column C Values into Column B When No Full-Row Search Is Needed
Use this lighter script if the only goal is to replace column B values with column C values in bulk, without searching the rest of each row.
Run Excel-Compatible VBA Find and Replace Macros in WPS Spreadsheets
WPS Spreadsheets supports VBA macros, so you can run this find-and-replace routine without leaving your familiar workflow. It opens Excel workbooks, preserves formulas, and handles .xls, .xlsx, and .xlsm files.
- 1. Download WPS Office: Go to the WPS Office download page and click Download WPS Office Free.
- 2. Open your workbook: Launch WPS Spreadsheets and open the Excel workbook containing the VBA macro.
- 3. Enable macros: If prompted, click Enable Content or Enable Macros so VBA code can run.
- 4. Run the macro: Press Alt+F11 to edit the code, or press Alt+F8 to open the macro list, select the macro, and click Run.
Frequently Asked Questions
Can I run this VBA macro in WPS Spreadsheets?
Yes. Open the workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, insert a module, paste the code, and run it. Adjust the worksheet name if needed.
Why does the macro use ws.Rows(i).Replace instead of replacing only column B?
Because the requirement is to search the entire row for the column B value and replace it with the column C value. This includes formulas and other cells in that row.
What do I change if my sheet is not named Sheet1?
In the code line Set ws = ThisWorkbook.Worksheets("Sheet1"), replace Sheet1 with your actual worksheet name, keeping the quotation marks.
Does the macro skip empty values in columns B or C?
Yes. The check If Len(ws.Cells(i, "B").Value) > 0 And Len(ws.Cells(i, "C").Value) > 0 prevents replacement when either the search value or replacement value is empty.
How can I stop the macro after column A data ends?
The macro calculates lastRow with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, so it only processes rows from row 1 to the last non-empty cell in column A.
