Replace #REF! Errors in Formulas with VBA in WPS Spreadsheets
Question details
Use VBA in WPS Spreadsheets to inspect selected formula cells that return #REF! errors, locate the #REF! text in those formulas, and replace it with the current column letter and the row number stored in Rev_Row while skipping cells without the error.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user needs an automated VBA routine to repair selected formula cells that return #REF! errors, replacing the broken reference text with the current column letter and a row number stored in Rev_Row.
- Observed behavior
- Selected formula cells contain #REF! errors; cells without the error must be skipped.
Open the workbook in WPS Spreadsheets and select the worksheet that contains the #REF! formula cells. Confirm that the Rev_Row variable or named cell is available and holds the intended row number before running any macro.
Inspect Selected Formula Errors and Replace #REF! Text with VBA
Use this solution when selected cells include formulas that evaluate to #REF! and you want to replace only the #REF! portion with the current column letter and Rev_Row.
This VBA approach uses SpecialCells to isolate selected formula error cells, then checks for the #REF! error with CVErr(xlErrRef) before editing the formula. It avoids changing other formula errors and restores screen updating after the operation.
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 where the replacement routine will be placed.
Paste the following macro into the module: Sub ReplaceRefErrors() Dim rng As Range Dim cell As Range Dim colLetter As String Application.ScreenUpdating = False On Error Resume Next Set rng = Selection.SpecialCells(xlCellTypeFormulas, xlErrors) On Error GoTo 0 If rng Is Nothing Then Application.ScreenUpdating = True Exit Sub End If For Each cell In rng If cell.Value = CVErr(xlErrRef) Then colLetter = Split(cell.Address, "$")(1) cell.Formula = Replace(cell.Formula, "#REF!", colLetter & Rev_Row) End If Next cell Application.ScreenUpdating = True End Sub
Close the VBA editor, return to the worksheet, and select only the cells that may contain #REF! formula errors.
Press Alt+F8, select ReplaceRefErrors from the macro list, and click Run to process the selected formula cells.
Replace #REF! Formula Text with VBA in WPS Spreadsheets
Use a short VBA routine in WPS Spreadsheets to target only selected formula cells returning #REF!, replace the #REF! segment with the current column letter and Rev_Row, and leave all other cells unchanged.
- 1. Open the workbook: Launch WPS Office, open the workbook with the affected formulas, and select the range containing formula errors.
- 2. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic to open the WPS VBA editor.
- 3. Insert and run the routine: Click Insert > Module, paste the macro that uses SpecialCells(xlCellTypeFormulas, xlErrors) and CVErr(xlErrRef), then press F5 or click Run.
- 4. Verify the repaired formulas: Return to the worksheet and check that #REF! portions now show the current column letter and Rev_Row while other cells remain unchanged.
Frequently Asked Questions
Why does the macro skip some selected formula error cells?
The routine targets only cells whose error value matches CVErr(xlErrRef), so it skips other formula errors such as #VALUE!, #N/A, and #DIV/0!.
How do I make Rev_Row available to the VBA macro?
Declare Rev_Row as a public variable, reference it from a named cell, or replace Rev_Row with the intended row number before running the routine.
What should I do if the column letter is not extracted correctly?
Check the address format returned by WPS Spreadsheets. The sample code uses Split(cell.Address, "$")(1); if your address format differs, adjust the delimiter or extraction expression accordingly.
Does this work in WPS Spreadsheets with macro-enabled Excel files?
Yes, WPS Spreadsheets supports VBA macros and can open .xlsm workbooks. Test the macro on a copy of the workbook before processing important data.
Will the macro replace every #REF! in the whole workbook?
No, it processes only the currently selected cells and replaces the #REF! portion only in selected formula cells that evaluate to #REF!.
