VBA & Macro Problems

Create Sequential Numbers by Letter and Area in Excel VBA

Huma Ashraf ChHuma Ashraf Ch Nov 3, 2026 870 views

Question details

The user needs to generate custom auto-incrementing sequence numbers based on specific letter and area combinations using an Excel VBA form.

How to Create Sequential Numbers by Letter and Area Using Excel VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Design the UserForm

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.

2
Define the Search Logic

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.

3
Extract the Maximum Sequence

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.

4
Generate the New ID

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.

Build a VBA UserForm to Auto-Generate Sequence Numbers
Testing Your Code: It is highly recommended to test your macro on a dummy dataset with the actual headers and table structures before deploying it to your production workbook.
Advanced Data Management

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. 1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the sequential numbering UserForm.
  2. 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. 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.
Fully compatible with Microsoft Excel macro-enabled workbooks (.xlsm).Robust engine for executing VBA UserForms and sequential numbering scripts.Lightweight architecture ensures large datasets process quickly without lag.Familiar user interface makes migrating your existing Excel files completely seamless.
microsoft office alternative - wps office

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.