Hi all,

I need help creating macro for a formula which I have in the attached file. This formula (in cell C2) is generating a product number based on the selected product type in the cell B2, and number of already existing product from that type (range C6:C97).


The code is made of 12 characters.
- First characters are allays "Product_"
- then is followed by a letter which (according the given table in sheet "Pickup_list" in cells H1:O6) describing the product. For example, product 1 always start with letter A, B, C, D or E. Product 2 Always start with F, G, H or I.
- last 3 characters are alphanumerical which are given subsequently, starting with 0-9 and then continuing from A-Z. For the first product of a type, this characters will be 001, then 002, 003 and so one. After 009 will came 00Z, then 00B, 00C.
- after are used all combination for Product1 which starts with letter A, we'll move to codes which are starting with letter B (as shown below).

Product_A001
Product_A002
Product_A003
…
Product_A009
Product_ A00A
Product_ A00B
Product_ A00Z
Product_ A010
Product_ A011
…
Product_A01Z
Product_ A020
…
Product_AZZY
Product_ AZZZ
Product_ B000
Product_ B001
Product_ B003


The formula in cell C2 in sheet is doing exactly this. But I need instead formula in the sheet, to have formula in macro which will create this code and show it in a TexBox in the UserForm.

I've inserted in the file a UserForm (activated after is pressed green button named "Show input window"), in which, after selecting the "Product Type" from the drop down menu, I need in the "Product Number" box to be displayed the code based on above mentioned calculation.

Thank you in advance for the help.
Igor


VBA formula.xlsb