Item Code created from Product Name and Color VBA

We've got this semi-solved using formula, but ran into issues as I need this to work in Chronological order, not placement order.

I have products and colors. Each product is a fruit in this example. There are thousand of different fruits. Each fruit has a color. There are thousands of different colors. Each color is unique to each fruit.

Each product gets a random 5 digit code assigned to it. Each color gets a 2 digit code assigned to in in numerical order. This makes up a code of product and color, example 52362-01

Example.
Apple (product) Red (color) makes the code 52362-01
if I have a Apple Yellow, the code would be 52362-02
Apple Green would be 52362-03 and so on (04, 05, 06 as the new colors are entered).

If I have a Banana Yellow, it would be a random 5 digit number again for the product 92623-01 as yellow is the first color to be added.
If there is Banana Green, it would be 92623-02 as it was the second one to be generated.

I want to be able to enter the product name (apple) in column A and the product color (red) in column B, and generate the 5 digit random product code (not numerical) with the 2 digit color code (red) in numerical order. (01, 02, 03, 04...etc.) combined in column C.

I want the code to be generated only once. If someone has deletes Apple Yellow, (52362-02) I want the code 52362-03 to remain for Apple Green. If someone adds a new color apple (Purple) I want to assign a new color code, not re-use the delete Yellow (02). So Apple Purple would be 52362-04. If someone adds another Apple Yellow later, it would get the same code it had before, 52362-02.

I know this sounds like a hard one, and in this thread http://www.excelforum.com/excel-gene...88#post3264888 spencer101 had it figured near 100% using the formula method. The only snag came when we entered product in chronological order, it would change the codes.

Very excited to see if this can be done via VBA!

Looking forward to your reply,

Try this. Should fire once you enter the color into column B. If it works I'll gladly take a click on the star

Please Login or Register  to view this content.

Thanks!!!! Will add star for sure!!

Can you post the worksheet with the VBA? I can't seem to get it to work.

I went alt+f11, insert module, save as Macro-Enabled Workbook and tried entering my product in A1, color in B1. I'd imagine C1 should spit out the code? Am I doing this right?

Let me know,

Ahh. This one doesn't go under an "insert module". After you hit alt+F11 (to get into the VBA editor) you should see the worksheets to the left along with the workbook and possibly the modules that you added. You would paste this code within the sheet that you want it to work on. Example under Sheet1(sheet1). Let me know if you are still confused, i can get a screenshot.

Thanks stnkynts.

Here is my screenshot...I think I'm doing it right, but still does not work.

vbatest4_zpsde0a4f8f.jpg

I'd imagine C1 would spit out the

You got it right. I assumed you had a header though so I coded it not to trigger if something was put into row 1. This line here:

Please Login or Register  to view this content.

Either remove that line or try entering it into row 2 and see what happens

Problemo solved!!!

Thank you! Star left! Thread solved!

Can you add me one more thing to the VBA.

In Column A: Product
In Column B: Color
In Column C: Size L
In Column D: Size W
In Column E: Product Code

Same as before, but the final code will move to Column E. Column C and D are size, numerical what will be added to the code.

To make things more interesting, the size can only be 2 digit. If its from 0.1 to 0.9 = 01. If its 1 to 1.49 = 01. If 1.5 to 2= 02 and so on.

Example, Apple Red 6L x 12W = 52362-01-0612

Example, Apple Red 12Lx12W = 52362-01-1212

Example, Apple Red 0.4Lx12W= 52362-01-0112

Example, Apple Red, 0.8Lx12W= 52362-01-0112

Example, Apple Red, 2.5Lx12W= 52362-01-0312

Can you rig this into the VBA?

Please and thank you!!!

Anyone?

Please and thank you!!!

Got it figured. Just made Column C hidden, then used CONCATENATE to add the sizes into it.

The main code you had figured before was the hard part. Thank you for doing that!

All good to go. Solved yet again!

Ok. Did not work as good as I thought with the CONCATENATE.

If I have 2 products, Apple Red 6x12 and Apple Red 12x12 the VBA does not like the code because it already exists as it does not look at size.

The codes generated would be 52362-01 and 52362-01, and then I used CONCATENATE...but because 52362-01 is 2 times, the VBA would not allow it. I like the fact that the VBA does not allow duplicates...so it would be awesome if the VBA was able to include the size.

The Order of entry is Product, Color, SizeL, and Size W. (Column A, B, C, D)

You are the super man if you can do this...This was an afterthought. I did not know it would require complete re-writing of the code to add...sorry but thank you if you can pull it off!

Don't know if it matters, but the Size columns can also be alphabetical for products that are random length. Example, I may have Apple Red 6 long x RL (Random Length) the code would look like 52362-01-06RL

Thanks a billion!!!!!

stnkynts, did you have any success?

Should I make a new thread as this one has now changed from the original.

Let me know,

I created a modified, final version of this problem in this thread...

http://www.excelforum.com/excel-prog...75#post3295575

Perhaps someone can help me figure it out?