How to Use VBA to Select a Cell from an ADDRESS Formula in WPS Spreadsheets
Question details
The user needs to make a VBA macro in WPS Spreadsheets read the current cell address returned by an ADDRESS formula instead of using a previously recorded address.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has an ADDRESS formula that returns a cell reference and wants the macro to select that cell dynamically each time it runs.
- Observed behavior
- The macro currently selects a previously recorded address instead of reading the current cell address returned by the ADDRESS formula.
Open the WPS Spreadsheets workbook that contains the ADDRESS formula, and note the cell that displays the formula result, such as C1. Make sure you can access the Developer tab and that macros are enabled for the workbook.
Read the ADDRESS Formula Result and Select the Cell with VBA
Use this method when the result of the ADDRESS formula is in a known cell such as C1 and you want the macro to select that cell dynamically every time it runs.
The ADDRESS formula returns a text value such as $A$1. Instead of hardcoding the previous address, the macro reads that text from the result cell and passes it to Range(...).Select so the selection follows the formula.
Open WPS Spreadsheets, click the Developer tab, and select Visual Basic to open the Visual Basic Editor.
In the Project Explorer, right-click the workbook project, choose Insert, and click Module to create a new module.
Copy and paste the following code into the module window: Sub MoveToAddress() Dim address As String: address = Range("C1").Value: Range(address).Select: End Sub
Click Run > Run Sub/UserForm or press F5 to execute MoveToAddress and select the cell returned by the ADDRESS formula.
Use a Compact One-Line VBA Macro
Use this shorter version when you only need a minimal macro and the address result is stored in C1.
Make Your WPS Spreadsheets Macro Read a Dynamic ADDRESS Formula
WPS Spreadsheets lets you run VBA macros that read the current cell address returned by an ADDRESS formula and select that cell, keeping your automation aligned with changing worksheet data.
- 1. Download WPS Office: Go to wps.com and click Download WPS Office Free, then install WPS Spreadsheets on your computer.
- 2. Open the workbook with the ADDRESS formula: Launch WPS Spreadsheets and open the Excel-compatible workbook that contains the ADDRESS formula and its result cell, such as C1.
- 3. Open the Visual Basic Editor: Click the Developer tab and choose Visual Basic. If the Developer tab is not visible, enable it from File > Options > Customize Ribbon.
- 4. Add the dynamic selection macro: Insert a module and enter: Sub MoveToAddress() Dim address As String: address = Range("C1").Value: Range(address).Select: End Sub, changing C1 if your formula result is elsewhere.
- 5. Run the macro: Press F5 or click Run > Run Sub/UserForm and confirm that the macro selects the cell address currently returned by the ADDRESS formula.
Frequently Asked Questions
Does this VBA code work in WPS Office Spreadsheets?
Yes. WPS Spreadsheets supports Excel-compatible VBA macros. Click the Developer tab, select Visual Basic, add the code in a module, and press F5 to run it.
What should I change if the ADDRESS formula result is not in C1?
Replace both C1 references in the VBA code with the actual cell containing the ADDRESS formula result, for example Range("D10").Value or Range(Range("D10").Value).Select.
Why does my macro still select the old cell after the formula changes?
The macro is probably using a previously recorded address. Use Range("C1").Value inside Range() so it reads the current formula result each time the macro runs.
How do I enable the Developer tab in WPS Spreadsheets?
Click the File menu, choose Options, open Customize Ribbon, and check Developer in the right-side list. Click OK to show the Developer tab.
Can I select a cell on another sheet with this VBA method?
Yes. If the ADDRESS formula returns a reference that includes a sheet name, such as Sheet2!$A$1, Range(address).Select can select that cell, provided the target sheet is not protected.
