Use VBA in WPS Spreadsheets to Classify Product Gender from a Reference List
Question details
The user needs a WPS Spreadsheets VBA macro that reads product titles and fills blank Gender cells by matching keywords from an editable reference sheet, in priority order.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Product data is imported with blank Gender cells, and classifications need to be automated according to a changeable keyword reference list such as Boy, Girl, Junior, Women, Men, and Unisex.
- Observed behavior
- The macro should search the reference list in order, write the corresponding gender value when a product title contains a keyword, and leave the cell blank when no keyword matches.
Open the workbook in WPS Spreadsheets and confirm that the GenderRef sheet contains the keywords in the desired priority order, with the output value in the adjacent column. The product data should already include a Gender column where blank cells can be completed.
Create the GenderRef reference sheet and run the VBA classification macro
Use this solution to automate the entire classification workflow while keeping keywords and output values editable in the worksheet.
The macro loops through each blank Gender cell, checks the product title against each keyword on GenderRef using InStr with vbTextCompare, and writes the first matching output value. The worksheet-based reference list lets you change priority or add terms without modifying the code.
In WPS Spreadsheets, press Alt+F11 or click the Developer tab and choose Visual Basic to open the VBA editor.
In the VBA editor, click Insert > Module to create a new standard module where the macro will be placed.
Add a subroutine that stores the product title and loops through blank Gender cells, then loops through the GenderRef rows from top to bottom and uses InStr(1, productTitle, keyword, vbTextCompare) > 0 to detect each keyword.
Place the cursor inside the subroutine and press F5, or click Run > Run Sub/UserForm. The matching gender value is written into the blank Gender cell, and unmatched cells remain blank.

Adjust the GenderRef priority order without editing the VBA code
Use this when you need to change which keyword takes precedence or add and remove search terms after the macro is already set up.
Classify product gender in WPS Spreadsheets with an editable VBA reference list
WPS Spreadsheets supports VBA macros, so you can open your workbook, use the built-in editor to add the classification routine, and keep the keyword-to-gender mapping fully editable in a worksheet. No hard-coded rule changes are required.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the product data and GenderRef sheet.
- 2. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic to access the macro editor.
- 3. Add the classification code: Insert a module and paste the subroutine that loops through blank Gender cells and the GenderRef keyword order.
- 4. Run and save: Run the macro with F5, then save the workbook as a macro-enabled .xlsm file to preserve the VBA code.

Frequently Asked Questions
Where should I store the gender keywords in WPS Spreadsheets?
Store them on a sheet named GenderRef in priority order, with the keyword in one column and the output value in the adjacent column. The macro reads this order at run time.
How do I make the macro match keywords without case sensitivity?
Use InStr(1, productTitle, keyword, vbTextCompare) so that BOY, Boy, and boy all match the same keyword.
What happens if a product title contains multiple keywords?
The first matching keyword in the GenderRef priority order wins. The macro writes that output value and then moves to the next blank Gender cell.
Why are some Gender cells still blank after running the macro?
The code intentionally leaves a cell blank when no GenderRef keyword is found. Add the missing keyword to GenderRef and rerun the macro.
Can I change the classification order without editing VBA code?
Yes, reorder rows on the GenderRef sheet. Because the macro loops from top to bottom, the new row order applies the next time you run it.




