How to Create an Excel VBA Macro to Parse Operation Cost Records in WPS Office
Question details
The user needs an Excel VBA macro that scans a worksheet from top to bottom, finds cells beginning with the phrase Operation Costs of Item, reads the related value from the cell to the left, splits the remaining text into columns, and writes the parsed output without clearing unrelated cells.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains lines that start with Operation Costs of Item, and the user wants to extract each related value, split the remaining text after the colon into columns, and process each item automatically using VBA.
- Observed behavior
- The goal is for the macro to locate matching cells in the used range, read the value one cell to the left, paste the required output five rows below into Column A, split the remaining text into Columns B and C, write related numeric values to Column D, and leave all other cells unchanged.
Open the workbook in WPS Office Excel and save it as a macro-enabled workbook (.xlsm) so the VBA code is retained. Review the worksheet to confirm the target rows start with Operation Costs of Item and that the value to copy appears one cell to the left of the matching text.
Create a VBA Macro to Parse Operation Cost Records in WPS Office Excel
Use this approach when you need to scan the used range, extract values next to matching operation cost cells, split item text into separate columns, and write results only to the intended output locations.
This macro loops from the first used row to the last used row and checks whether each cell begins with the target phrase. It writes only to the output cells, so unrelated data remains untouched.
Launch WPS Office Excel, open the target workbook, and save it as an Excel Macro-Enabled Workbook (.xlsm) if it is not already macro-enabled.
Press Alt+F11 to open the Microsoft Visual Basic for Applications editor in WPS Office Excel.
In the Project Explorer, right-click the workbook or VBAProject, choose Insert > Module, and open the new module window.
Insert a VBA Sub that sets the used range, loops through cells, checks whether each cell text starts with Operation Costs of Item, reads the value one cell to the left, and writes the required output five rows below using Split on two or more spaces.
Click Run > Run Sub/UserForm or press F5, then return to the worksheet and confirm that only the intended output cells are populated.
Use a VBA Macro in WPS Office Excel to Parse Operation Cost Records
WPS Office Excel can run VBA macros that scan worksheets for operation cost lines, split text into columns, and write results precisely to the intended output cells without disturbing existing data.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Office Excel from the WPS Office home screen.
- 2. Open the workbook: Open the Excel workbook that contains the operation cost records, and save it as a macro-enabled workbook if macros are required.
- 3. Open the VBA editor: Press Alt+F11 to open the VBA editor and insert a module for the parsing macro.
- 4. Run the macro: Paste the macro code and run it with F5 or Run > Run Sub/UserForm.
- 5. Check the output: Return to the worksheet and verify the parsed values appear five rows below the matched source row in Columns A through D.
Frequently Asked Questions
Can WPS Office Excel run VBA macros that parse worksheet data?
Yes. Open the workbook in WPS Office Excel, press Alt+F11 to open the VBA editor, insert a module, paste the macro, and run it with F5. Save the file as a macro-enabled workbook (.xlsm) if you need to keep the VBA code.
How can the macro split the remaining text after the colon into separate columns?
Use the VBA Split function on the text after the colon. In this scenario, split with a delimiter of two or more spaces, then write the resulting first field to Column B and the next field to Column C.
Where should the macro place the parsed output?
The accepted approach places the required output five rows below the matched source row in Column A. The split text goes to Columns B and C, and each additional related value goes to Column D.
How do I prevent the macro from clearing unrelated cells?
Write only to the intended output cells for each matched source row. Do not use commands such as ClearContents or Clear on entire rows or large blocks; assign values only to the specific destination addresses.
What should I do if the macro does not run in WPS Office Excel?
Confirm that the workbook is saved as a macro-enabled format and that macro security settings allow VBA macros. In WPS Office Excel, use Alt+F11 to verify the code is in a module, then run it with F5.
