Create Sequential Numbers by Letter and Area in Excel VBA
Question details
The user needs to generate custom auto-incrementing sequence numbers based on specific letter and area combinations using an Excel VBA form.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using a VBA UserForm to collect equipment details (letter, area, description) and automatically assign a unique ID that increments per category (e.g., A-01-01, A-01-02).
- Observed behavior
- The goal is to successfully scan existing entries for a matching prefix, find the highest existing sequence number for that combination, and assign the next number correctly.
Ensure that the Developer tab is enabled in your Excel ribbon and save your file as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.
Build a VBA UserForm to Auto-Generate Sequence Numbers
Create a VBA script that captures the prefix from the user, searches the existing data column for the highest matching sequence, and adds one to create the new ID.
When managing categorized equipment data, standard auto-incrementing rows aren't sufficient. You need custom VBA logic that reads the input prefix (like 'A-01'), scans previously entered IDs for that exact prefix, extracts the highest numerical suffix, and assigns the next available number.
Using a UserForm ensures data entry remains standardized, reducing human error when manually typing complex alphanumeric sequence codes.
Press ALT + F11 to open the VBA Editor. Go to Insert > UserForm. Add TextBoxes for the Equipment Letter and Area Number, and a CommandButton to submit the data.
Double-click the CommandButton to open its code module. Write a script to loop through the existing ID column to find all cells that begin with the concatenated Letter and Area prefix.
Inside the loop, use the VBA Right() or Split() function to isolate the last two digits of matching IDs. Keep a running tally of the highest number found in a variable.
Add 1 to the highest sequence number found. Use the Format() function to ensure two digits (e.g., Format(newNumber, "00")). Concatenate the Letter, Area, and new Number, then write it to the next blank row in your worksheet.

Use Formula Alternatives (Without VBA)
If VBA is too complex or restrictive, you can generate sequential numbers dynamically using Excel formulas like COUNTIFS.
Run Macros and VBA Smoothly in WPS Spreadsheet
WPS Spreadsheet provides excellent support for VBA macros and complex formulas, allowing you to run custom auto-numbering scripts and UserForms with complete ease and high performance.
- 1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the sequential numbering UserForm.
- 2. Enable the Developer Tab: Navigate to the Options menu to enable the Developer tab if it isn't already visible on your ribbon.
- 3. Execute the UserForm: Click on 'Macros' or 'Visual Basic' to access your code, or simply click your assigned form button to start generating sequential numbers.

Frequently Asked Questions
How do I ensure my sequence numbers always display as two digits (e.g., 01, 02)?
In VBA, you can force a number to display with leading zeros using the Format function. For example, using Format(nextSequence, "00") ensures that the number 1 is formatted as "01".
Why does my VBA code reset the sequence back to 1 for existing combinations?
This usually occurs if your loop is searching the wrong column, or if the string matching logic fails to recognize the existing prefix. Double-check your column references and ensure there are no hidden trailing spaces in your worksheet data.
Can I share a workbook with a VBA UserForm for others to use?
Yes, as long as you save the file as a Macro-Enabled Workbook (.xlsm). However, the users opening the file must ensure they click 'Enable Content' or 'Enable Macros' when prompted by their spreadsheet software, otherwise the UserForm will not function.




