Automatically assign a code number to a list of products?

[ppgab]

There's a table with products, and I would like that whenever I add a new product to it, Excel automatically adds an unique code number to column A, based on the highest value found on that table column+1

Its not in sequence since I re order the products constantly

And it can't be based on the row number since I remove products too, and I don't want an already asigned code to change

[ChemistB]

Okay, you cannot do this with formulas since the formula will recalculate once you remove that number. It will need to be done with VBA. Can you upload an example of how it will be (a spreadsheet) so that I have something to work with? Go Advanced>Manage Attachments

[ppgab]

Okay, you cannot do this with formulas since the formula will recalculate once you remove that number. It will need to be done with VBA. Can you upload an example of how it will be (a spreadsheet) so that I have something to work with? Go Advanced>Manage Attachments

Ah ok, if VBA is the only way then I can do it, just loop through A and find highest number

I was just wondering, a button would be really unpleasant, can the module be added to an automatic event on the table or something like that?

[Ochimus]

ppgab,

Yes, the attached Macro runs automatically every time you add something to Col B.

Please Login or Register  to view this content.

Add it to worksheet, NOT a module.

Hope it helps

[ppgab]

@Ochimus Thanks for your time friend, I added it but it only adds the code 109 for some reason, I've added the workbook as attachment, is the "produtos" sheet

[ChemistB]

Near the end of Ochimus' code is

Please Login or Register  to view this content.

Modify that depending on what sheet you are in.
Ochimus's code does not "remember" numbers that you've erased however. I would suggest that you assign a cell to track that, maybe somewhere off to the right, i.e. cell W1. then change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Does that help?

[ppgab]

@ChemistB Its the same sheet, so couldn't it be just

Please Login or Register  to view this content.

?

Or better yet, I was thinking, the table is called "Table44" , is there a function or global var that returns the number of rows a table has? So it would be more efficient than Range("A1:A1000")

Thanks for all the help, I'm learning a lot

[Ochimus]

ppgab,

I have no idea why the macro keeps creating "109" on your file.

As you can see on the attached, I copied your "produtos" sheet, and used the same Macro,

If you add new product to the attached. everything works perfectly!

Ochimus

[ppgab]

@Ochimus it's because the rng was taking range from sheet1, my list is actually on sheet2 but it's working just fine now, thanks pal

[Ochimus]

ppgab,

Glad it's sorted out, Teach me a lesson about using the "active sheet" instead of the sheet number in future!

Thinking about your question over the "set" range limit, the simple answer is to set a "dynamic" range for the "Descrição".

If you are not familiar with it,

Click the Formulas tab.
In the "Defined Names" group, click Name Manager.
Click New.
In the Name box, type Descrição.
In the Refers to box, type the following text, and then click OK:
=OFFSET($B$2,0,0,COUNTA($B$:$B),1)
(Being dynamic, it will resize automatically as you add more names).

Then replace the row numbers in the Macro with the Range Name.

Ochimus