VBA & Macro Problems

How to Create an Excel Macro to Generate Product Sizing Data

Camila Milosovich Oct 10, 2026 868 views

Question details

The user wants to create a VBA macro to replace numerous INDEX/MATCH formulas for populating sizing data based on product subcategory, department, and fit values.

How to Create an Excel Macro for Product Sizing Data

Product: Excel
Device & OS: not provided
Scenario: Automating the generation of product sizing data by transitioning from resource-intensive worksheet formulas to an automated VBA script triggered by a button.
Observed behavior: The current setup relies on complex INDEX/MATCH formulas. To provide a precise VBA alternative, the exact workbook structure, sample data, and desired output layout are needed.

Before you start

Before writing the macro, duplicate your workbook to create a safe testing environment. Make sure to remove any confidential client or business data if you plan to share the file on forums for further coding assistance.

Solution 1Recommended

Develop and Assign a VBA Macro to Automate Sizing

This foundational method outlines how to replace worksheet lookup formulas with a VBA script that reads your criteria and populates the sizing output range.

Writing a VBA script allows you to calculate data in the background and output static values, greatly reducing calculation lag compared to thousands of live INDEX/MATCH formulas. Because the exact code depends on your specific rows and columns, follow these structural steps to build your automation.

Open the VBA Editor

Press ALT + F11 to launch the Visual Basic for Applications Editor. Go to Insert > Module to create a blank canvas for your new macro, and type 'Sub GenerateSizingData()' to begin.

Define Your Variables and Ranges

Declare your variables for the source worksheet, lookup tables, and output ranges. Use loops (such as 'For Each cell in Range') to iterate through the rows containing your product subcategory, department, and fit values.

Implement the Lookup Logic in VBA

Use 'Application.WorksheetFunction.Index' and 'Application.WorksheetFunction.Match' within your loop to evaluate the criteria and retrieve the matching size percentages from your lookup tables.

Write Values to the Output Range

Instruct the macro to write the retrieved values directly into the output cells (e.g., 'cell.Offset(0, 1).Value = result'). This pastes the data as static text, eliminating the need for formulas.

Assign the Macro to a Button

Return to your worksheet, insert a shape or Form Control button, right-click it, select 'Assign Macro', and choose 'GenerateSizingData'. You can now click this button to run your sizing automation.

Develop and Assign a VBA Macro to Automate Sizing

Tip for Complex Lookups: If you are matching multiple criteria across massive datasets, consider loading your lookup tables into VBA Dictionaries or Arrays. This is significantly faster than querying the worksheet repeatedly.

Solution 2

Prepare a Sample Workbook for Expert Assistance

If you require custom code written by others, sharing a screenshot is insufficient. You must provide an anonymized sample file.

Automate Sizing Data with WPS Spreadsheet Macros

WPS Spreadsheets provides robust support for VBA macros, allowing you to seamlessly replace heavy INDEX/MATCH formulas with automated scripts. You can write, edit, and run your macros in a familiar environment.

1. Enable the Developer Tab: Open WPS Spreadsheets, go to the Developer tab on the ribbon to access macro tools.
2. Launch the VBA Editor: Click on 'Visual Basic' or 'Macro' to open the built-in VBA editor.
3. Insert Your Code: Add a new module, paste your sizing data generation macro, and close the editor.
4. Save as Macro-Enabled: Go to Menu > Save As, and choose the '.xlsm' format to ensure your macro runs flawlessly next time.

Fully compatible with Microsoft Excel macro formats (.xlsm, .xlsb).Built-in VBA editor to write, test, and debug complex data generation scripts.Lightweight architecture handles large datasets and complex lookup loops smoothly.Cost-effective solution with powerful developer tools for daily office automation.

microsoft office alternative - wps office

Frequently Asked Questions

Why should I replace INDEX/MATCH formulas with a VBA macro?

Heavy use of complex lookup formulas like INDEX/MATCH across thousands of rows can significantly slow down workbook calculation times. A VBA macro calculates these values in the background and pastes them as static values, drastically improving file performance.

How do I assign my new macro to a 'Generate Sizing' button?

Go to the Developer or Insert tab, select 'Shapes' or 'Form Controls', and draw a button on your sheet. Right-click the newly drawn button, choose 'Assign Macro', select your sizing macro from the list, and click OK.

Why does a screenshot not help experts write the macro for me?

Screenshots only show the visual layout. They do not provide the underlying workbook structure, named ranges, specific formula syntax, or data types required to accurately write and test VBA lookup loops.

What file format do I need to save my macro-enabled workbook?

You must save your file as an Excel Macro-Enabled Workbook (.xlsm) or an Excel Binary Workbook (.xlsb). Saving it as a standard .xlsx file will permanently remove all your VBA code.

WPS Office