Getting next UPC Number

[johnny45]

I'm using xl to generate UPC numbers.

The first digit (from the left) is the UPC type.
The next five digits are the Manufacturer code.
The next five digits are the product code which is assigned by the manufacturer.
The final digit is the check digit.
so if my lists consists of:
xxxxxx10000x
xxxxxx10001x
xxxxxx10002x
I would like to be able to get the next number which would be:
xxxxxx10003x
What would be the most efficient way to get it ?

 

[SkipVought]

Hi,

Have a variable for UPC type and Manufacturer code and then start the Product code at 10000 and incriment by on for each product.

Concatenate to form the final result.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[johnny45]

Thanks Skip,
SO as not to confuse the user I guess I can hide the column containing that value ?

ColA - 1000 (hidden)
ColB - vendor SKU
ColC - UPC number ( UPC type & Manufacturer code &application.max(cola)+1 & check digit)

 

[SkipVought]

It can be on an entirely different sheet (HIDDEN if so desired)

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Bong]

Why hide anything? First of all, it sounds like you're doing this on the spreadsheet, not in a macro, right?

You mention a "user"; what will the user be doing? I would put the UPC type in colA (row 1); the Manufacturer code in Col B (row 1); "1000" in ColC (row 1); and the check digit in ColD (row 1). Then for Columns A, B, and D, row n = row n-1 (n>1). For Column C, row n= (row n-1)+1 (n>2). Then adjust your column widths until it looks like you want it to.

_________________
Bob Rashkin

 

[johnny45]

Thank you both I think I got it now