VBA & Macro Problems

Use VBA in WPS Spreadsheets to Classify Product Gender from a Reference List

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 869 views

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.

Use VBA in WPS Spreadsheets to Classify Product Gender from a Reference List
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In WPS Spreadsheets, press Alt+F11 or click the Developer tab and choose Visual Basic to open the VBA editor.

2
Insert a module

In the VBA editor, click Insert > Module to create a new standard module where the macro will be placed.

3
Paste the classification macro

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.

4
Run the macro

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.

Create the GenderRef reference sheet and run the VBA classification macro
Classification completed: The cell is populated only by the first matching keyword in the GenderRef priority order, so reordering the reference rows changes the result without editing code.
WPS Spreadsheets VBA automation

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. 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the product data and GenderRef sheet.
  2. 2. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic to access the macro editor.
  3. 3. Add the classification code: Insert a module and paste the subroutine that loops through blank Gender cells and the GenderRef keyword order.
  4. 4. Run and save: Run the macro with F5, then save the workbook as a macro-enabled .xlsm file to preserve the VBA code.
Automate gender classification using a priority-ordered reference list that is easy to update in WPS Spreadsheets.Open, edit, and save .xlsm and .xlsx workbooks with VBA macro compatibility.Use a familiar spreadsheet and VBA editor interface without changing your existing Excel workflow.Free to download and lightweight, so you can run the macro without heavy system resources.
microsoft office alternative - wps office

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.