VBA to produce a unique and incremented order number in col A

**lday75** · 02-26-2013, 11:56 AM

I am looking for a way in VBA to enter a unique order number value I.E. DHOrd000x or VndOrd000x into the first blank cell in column A. I already have the code to enter in the rest of the order values, just looking for a way to generate a unique order number when submitting the order onto the sheet. The code for the submission I have now is...

Please Login or Register  to view this content.

Can this be done in code? Any help is greatly appreciated, thank you.

**stnkynts** · 02-26-2013, 12:03 PM

The first result of a google search yielded this:

Please Login or Register  to view this content.

Worked fine for me. I am assuming you know how to work with custom functions and how to assign a function to a cell value in code.

**lday75** · 02-26-2013, 12:07 PM

Does this only do random though? I need the order number to be unique, BUT incrementing. I.E. DHOrd00001, DHOrd00002, etc.

**stnkynts** · 02-26-2013, 01:45 PM

Please Login or Register  to view this content.

Each time you run the code it should increase the last number by 1 as per your example. I formatted it to have up to 5 digits with leading 0's when appropriate.

**lday75** · 02-26-2013, 02:05 PM

I pasted the code into my userform procedures in its own proc, called it from my save code, and am getting runtime error 13, Type Mismatch at the following line

Please Login or Register  to view this content.

**stnkynts** · 02-26-2013, 02:19 PM

You might want to specify a worksheet in front of the range. Also if the value in cell A1 of that sheet is less than 5 characters it will kick out an error. Other than that hard to say without looking at the sheet.

**lday75** · 02-26-2013, 02:37 PM

Well that might be part of the problem, there is not a value in that cell. I am entereing in values into the rows from a userform. So there is no value in the order Number field to start with. This is what I was trying to get done in code. I did add the sheet name to your code and I am still getting the same error. I also tried adding DHORD into the cell and it also errored out. Code below

Please Login or Register  to view this content.

My Save Code - doing some other testing as well, so the call to this proc is commented out at the time

Please Login or Register  to view this content.

**stnkynts** · 02-26-2013, 02:59 PM

I guess this is my bad. I assumed by your number of posts that you had some concept of how VBA works, but I was wrong. Just saying Sheet1 in front of the Range means nothing. Example: Sheet1.Range("A1") should be Sheets("Sheet1").Range("A1").

It still works if there is no value in cell A1 by setting the first number to 1. The only time I can see it getting hung up is if there is less than 5 characters in cell A1, which we could fix with using "Len". I assumed this would not be necessary though since you are going to be adding just 1 more number each time.

Try putting DHORD00001 into cell A1. I have tried this on a test workbook and it works for me ie becomes ?????00002. It will randomize the first 5 digits each time because it is not clear to me when you want the random part generated and when you dont. If we need to keep the first 5 letters constant that is easy to do but I need parameters on when it should stay the same and when it should change.