How to Use VBA in WPS Spreadsheets to Replace Formula Text with a Cell Value
Question details
The user needs VBA code that replaces a specific text string inside selected formulas with the value from cell A2, limited to the relevant range rather than the whole worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has formula cells in a workbook and wants to update only the selected formula cells with replacements sourced from cell A2.
- Observed behavior
- The previous macro replaced text across the entire worksheet like Find and Replace; the user needs a range-limited solution that does not change unrelated cells.
Open the workbook in WPS Spreadsheets and make sure the Developer tab is available. Identify the exact target range and the worksheet name, and confirm the replacement source cell, such as A2.
Replace Text in Selected Formulas with the Value from Cell A2 Using a Range-Limited VBA Macro
Use this method when you need to replace a text string only inside the formulas in the currently selected range, without affecting other cells.
The macro stores the value from cell A2 in a variable and passes it to the Range.Replace method. Because Replace is called on the selected range instead of the whole worksheet, unrelated cells are left unchanged.
In WPS Spreadsheets, press Alt+F11 or click Developer > Visual Basic to open the VBA editor.
In the VBA editor, click Insert > Module to create a new module for the macro.
Paste the following code in the module: Sub ReplaceFormulaTextWithCellValue() Dim rng As Range Dim replacementText As String Set rng = Selection replacementText = Range("A2").Value rng.Replace What:="OldText", Replacement:=replacementText, LookAt:=xlPart End Sub
Return to WPS Spreadsheets, select only the cells that contain the formulas to update, press Alt+F8, choose ReplaceFormulaTextWithCellValue, and click Run.
Replace Multiple Text Strings from a Replacement Table
Use this method when you need to replace several old text strings by keeping the replacements in a separate worksheet instead of hard-coding one value.
Use a Range-Limited VBA Macro to Replace Formula Text in WPS Spreadsheets
WPS Spreadsheets can run the VBA macro directly, letting you replace formula text in the selected range with the value from cell A2 while keeping other cells unchanged.
- 1. Open the workbook: Launch WPS Spreadsheets and open the workbook that contains the formulas to update.
- 2. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic.
- 3. Insert a module and paste the macro: Click Insert > Module, paste the range-limited VBA code, and save the workbook as a macro-enabled file if prompted.
- 4. Select the target formulas and run: Select the relevant formula cells, press Alt+F8, choose the macro, and click Run.
Frequently Asked Questions
How do I stop the VBA Replace from changing the entire worksheet?
Assign the target range to a variable, such as Set rng = Selection, and call rng.Replace instead of Cells.Replace or ActiveSheet.Cells.Replace.
How do I use the value from cell A2 as the replacement?
Store it in a string variable: replacementText = Range("A2").Value. Then use Replacement:=replacementText in the Range.Replace method.
Will this macro work in WPS Spreadsheets for an Excel workbook?
WPS Spreadsheets supports VBA macros in many Excel workbooks, including .xlsm files. Open the workbook in WPS Spreadsheets and test the macro because some Excel-specific features may differ.
Why does the replacement value appear blank when I record a macro and copy a cell?
The recorded macro likely uses a hard-coded string or ActiveCell.Paste. Use a variable that reads the source cell value and pass that variable to Replacement instead of recording paste actions.
