How to Retrieve Commodity Prices by ID in Excel VBA Using WPS Spreadsheets
Question details
The user needs a VBA user form in WPS Spreadsheets that returns a commodity price by ID and also handles cases where multiple commodity records match or no price exists.
- Product
- WPS Spreadsheets / Excel VBA
- Device & OS
- not provided
- Scenario
- Creating a spreadsheet VBA user form that accepts an ID, package code, and supplier code to retrieve the matching commodity price.
- Observed behavior
- The form should find matching commodity rows and either display a valid price or clearly handle duplicate matches and missing prices.
Prepare a sample workbook with the commodity worksheet layout, user-form controls, lookup fields, and expected output before writing or testing the VBA code. Confirm that macros are enabled in WPS Spreadsheets so the VBA project can run.
Create a VBA User Form That Looks Up Commodity Prices by ID, Package Code, and Supplier Code
Use this approach when you need a form to return a price after a user enters an ID, package code, and supplier code, and to handle multiple matching records or missing prices.
A VBA user form is the most flexible way to capture the three lookup keys and return the matching commodity price. Keep the lookup data in a dedicated worksheet table so the code can identify columns by name and handle duplicate matches.
Open WPS Spreadsheets and create a data sheet with columns such as CommodityID, PackageCode, SupplierCode, and Price, then enter representative records including duplicate commodity IDs and one row with no price.
Go to the Developer tab, click Visual Basic or Macro to open the VBA editor, choose Insert > UserForm, and add text boxes for ID, package code, and supplier code, a command button for lookup, and a label or list box for the result.
Double-click the lookup button, then in the click event read the three input values, loop through the data range with Do While or For Each, compare all three fields, collect matches, and show a 'No price found' message when the price cell is empty.
Press F5 in the VBA editor to launch the form, enter a known commodity ID, package code, and supplier code, confirm the price appears, then test a missing price and a duplicate-record case before saving as an .xlsm workbook.
Build a Commodity Price Lookup VBA Form in WPS Spreadsheets
WPS Spreadsheets can run VBA user forms and macros, so you can build a price lookup by commodity ID, package code, and supplier code without changing your existing Excel workflow.
- 1. Open the macro-enabled workbook in WPS Spreadsheets: Click File > Open and select your .xlsm or .xlsx workbook with the commodity data and user form.
- 2. Enable the Developer tab: Go to File > Options > Customize Ribbon and turn on Developer if it is not already visible.
- 3. Create or edit the UserForm: Press Alt+F11 to open the VBA editor, then insert or open the UserForm for commodity price lookup.
- 4. Add matched-row lookup code: In the button click event, read the ID, package code, and supplier code, then loop through the data range to find matching commodity records.
- 5. Run the form and save: Press F5 to test the user form, confirm missing-price and multiple-match handling, then save as an Excel Macro-Enabled Workbook (.xlsm).
Frequently Asked Questions
Why does my VBA lookup return only the first match instead of multiple matching commodity records?
Use a loop or a filtered range in WPS Spreadsheets VBA instead of a single Find call. Initialize a Collection or list to collect every row where ID, package code, and supplier code match, then display all matches in a list box or select the first active price.
How do I handle missing prices in the VBA user form?
Before assigning the price to the label or cell, check whether the price cell is empty or contains a placeholder value. If it is missing, show a message such as 'No price found' and stop the lookup instead of returning a zero-length string.
Which file format should I save the WPS VBA workbook in?
Save as Excel Macro-Enabled Workbook (.xlsm) so the user form and VBA project are retained when the file is reopened.
How do I open the VBA editor in WPS Spreadsheets?
Enable the Developer tab, then click Visual Basic or press Alt+F11. The VBA editor opens in a separate window where you can insert a UserForm and write the lookup code.
Can I use existing Excel VBA code in WPS Office?
WPS Office can run many Excel VBA macros and user forms, but you should test form controls and references on a copy of your workbook because feature support may vary.
