Create and Increment Numbers with Letter Combinations to make a serial number in VBA

wayneseymour

New Member
Joined
Dec 7, 2016
Messages
7
I have a data sheet that I input data from a user form. One aspect of the form is that as you finish adding the data and before you submit it to the data sheet a ID number is generated using the MAX function +1 in the VBA code which just increments 1,2,3,4. The user form is used to enter, add a Vendor (contractor,Owner or Company name), this is done via a combo box with the names and equipment type. some names are two or three words ,ie. "Bob Cat Services" or "Azan Heavy Equipment", "Ron Wilson"), but always a combo of more than one name. There is another combo box that holds the type of equipment owned or listed by the vendors,contractors("Backhoe","Roller","Dozer","Dump Truck"etc.) I would like to have a Label or text box that will, as the names,ie, vendor and equipment are entered it will create a unique ID Number... like for example "Bob Cat Services, uses BCS for the vendor, equipment like "Back-hoe" or Bh for the equipment followed by a number most likely starting out with 0001. Making the serial number; BCSBH0001. THIS WOULD CHANG DEPENDING ON THE NAME AND EQUIPMENT. but always using the first three letters or two of the vendor name like if its more than one word "B" from Bob, "C" from "Cat" and "S" from "services", then add the first two letters from the equipment BH for back-hoe or just the first two letters if its spelt "Backhoe". Then lastly adding 0001, to end up with a unique serial number BCSBH0001 which tells the company,vendor and the equipment type. This makes for each vendor the serial is different "Azan Equipment" would be AEBH0001, "Bob Cat Services" would be BCSBH0001 and so on. After that any addition entry would read the column of the ID in the data sheet and add the next sequential number belonging to that serial group , ie AEBH0001 would become AExx0002,(xx being the first two letters of the equipment type) or BCSxx0002 and so on. Granted something like "Dump Truck" may pose a problem. But thats the jist of what I'm trying to achieve. Hope someone can help. I'm new to VB so I find it hard to figure out the code. Please help. THANKS to everyone.
 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

pjoaquin

Active Member
Joined
Dec 12, 2007
Messages
372
Office Version
  1. 365
  2. 2016
Standardize your data entry and create lookup tables for both vendors and equipment. It will make the task of generating unique ID's much easier.

Your vendor lookup list should include columns for vendor code (that you decide upon), vendor full name, vendor contact info, etc. Your equipment list shouldn't include multiple spellings/variations of the same piece of equipment, e.g. don't let users type "back-hoe", "backhoe" or "Dump Truck" and "dumptruck", etc. Make them pick from a drop-down/data validation list.

Once you've standardized the input, you can create a table of vendors, equipment and generate unique serial #'s.
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
As you are already using lists however they are stored I would suggest adding an extra column in the data to include the short codes associated with each item it will take a while to setup but some will be sort of fixed list and some will expand
this will allow you to keep control and keep everything clean and eliminate any glitches so joining the letters together will become a simple task and carry on as you are with your numbering
also you could cover when companies amalgamated when bought out by assigning same 3 letter code to two different organisations
when a new item is added to any list just add the new codes
just my thoughts
 

Watch MrExcel Video

Forum statistics

Threads
1,127,301
Messages
5,623,855
Members
415,995
Latest member
SergioCM92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top