Generate Unique Serial Number

[Tbrown]

i want to be able to click "Generate ClientID" it generates a unique serial number that range from 1000 to 99999 and doesn't repeat

 

[Joe4]

Welcome to the Board!

Do you keep a list of them somewhere?
If so, why not just start at 1000, and take the maximum value add 1 each time?
So, if you were storing all the values in column A, it would just look like:
=MAX(A:A)+1

 

[Tbrown]

I have them stored on another sheet("sheet2"). Is there a way i can pull from sheet2 using vba and it deletes the used number to avoid repeats??

 

[Tbrown]

or is there a code to generate values between 1:100000000 and not repeat

 

[Joe4]

I have them stored on another sheet("sheet2"). Is there a way i can pull from sheet2 using vba and it deletes the used number to avoid repeats??

or is there a code to generate values between 1:100000000 and not repeat

I see that you are adding in conditions that weren't mentioned in your original post that change the problem immensely. You want to be sure to ask a good, complete question, so we know exactly what you are after and are not left guessing. Is there a good reason why these numbers must be random and cannot be sequential?

Your question is very much like another current question. I would recommend taking a look at this one here: Help In updating my code, Employee Number Generator
If you truly want a random number generator with no repeats, that is probably the route you are going to have to go.
If you can go with sequential numbers, you can make this much easier on yourself.

 

[Tbrown]

I apologize, i'm still learning. However, the numbers can be sequential!

 

[Joe4]

I apologize, i'm still learning. However, the numbers can be sequential!

OK, great. Then my original reply should work for you. You can use the MAX function in regards to data on another sheet. Just add the sheet reference, i.e.
=MAX(Sheet2!A:A)+1

 

[Sharid]

Tbrown

You can have a look here at my thread, I am also looking for a similar thing, I have the code, just need to sort out a duplicate issue. As I have heard that some codes can end up creating a duplicate and you will need to look into this as I am

My Thread Link

 

[Sharid]

Tbrown

See my above thread, it has the code in the LAST post. You might need to tweak it for your purpose.

Column A = Employee ID number
Column B = Employee names
Column C = Shows if there is a duplicate.

How the code works

1. When the command button is clicked, the code checks if there is data in column B, in this case the employees name.
2. It then assigns that employee and new employee ID e.g. EmpNo. 111111
3. Columns C shows if there is a duplicate, it will place a number in Column C anything more than 1 is a duplicate. I have been assured that it it will not produce duplicates as this was my main issue, therefore column C is only a backup

Hope this helps