4 digit random pin number

[pucky900]

I'm new to some of the more advanced features to Excel and trying to figure this out. I'm trying to create a 4 digit login pin number that does not repeat. I was able to create the random number using =Randbetween(1000 to 9999) but it creates repeating number combinations. How do I create something that will create unique non-repeating numbers.

 

[Al Chara]

Tushar, thanks for the links. The discussions were very interesting.

 

[pucky900]

Thank you so much for the help. As soon as I made the change, it worked fine. For some reason my browser is not letting me cut and paste for this message board, so I have to type it in. One thing I notice is that it will only put one number at a time when I run the macro. Is there a way to do 1500 at once. That is the amount that I have to do. Also, is there a good place to for me to learn what all that code means. I have no idea what you wrote or how it works. Once again... Thank you.

 

[Al Chara]

You could throw my code into a for loop. Try the following (I commented out all the lines so you would know what is happening):<pre>Sub PINCreate()
Dim RndNo As Long 'Declaring the random number variable
Dim Test As Long 'Declaring the variable that match will return
Dim i As Long 'Declaring the counter variable for the for loop

For i = 2 To 1501 'Starting the for loop that will loop 1500 times
Randomize 'Initializing the random number generator
Do 'Starts the do loop
RndNo = Int((9999 - 1000 + 1) * Rnd + 1000) 'Calculates a random number between 1000 and 9999
On Error Resume Next 'Bypassing the error that MATCH will create if there are no matches
Test = Application.Match(RndNo, Range(Cells(1, 1), Cells(i, 1)), 0) 'Testing for matches of RndNo in the worksheet
If Err.Number = 13 Then 'If statement for error, if there are no matches, the error will be 13
GoTo FoundUnique 'If there are no matches that means RndNo is unique
Err.Clear 'Clearing the error so it can be used again
End If 'Ending the if statement
Loop 'Makes the do loop restart
FoundUnique: 'This is where the code jumps to when a unique number (RndNo) is found
Cells(i, 1) = RndNo 'Sets the proper cell in the list equal to the RndNo
'FYI Cells(i,1) refers to the cell in row i and column 1
Next i 'Restarts the for loop
End Sub</pre>

Or you could follow the link posted by Tushar. It might be easier for you.
http://groups.google.com/groups?selm=MPG.1689584b91ef5e5298a3d4@msnews.microsoft.com

_________________
Kind regards,<font size="5">^{<span style="text-decoration: overline">AL</span>}<u>_CHARA</u></font
This message was edited by Al Chara on 2002-08-07 14:29

 

[pucky900]

Awsome.... works just like I wanted it to. Thank you so for your help.

 

[verluc]

On 2002-08-06 13:05, Al Chara wrote:
Try the following:<pre>Sub PINCreate()

Dim LstCell As Range
Dim Rng As Range
Dim RndNo As Long
Dim Test As Long

Set LstCell = Range("A65536").End(xlUp).Offset(1, 0)
Set Rng = Range(Range("A2"), Range("A65536").End(xlUp))
Randomize
Do
RndNo = Int((9999 - 1000 + 1) * Rnd + 1000)
On Error GoTo FoundUnique
Test = Application.WorksheetFunction.Match(RndNo, Rng.Address, 0)
Loop
FoundUnique:
LstCell = RndNo

End Sub</pre>
This will place unique numbers in column A. It also assumes that there is a heading in Cell A1.

_________________
Kind regards,<font size="5">^{<span style="text-decoration: overline">AL</span>}<u>_CHARA</u></font>

EDIT: Fixed part of the code. Had the variable name wrong.
This message was edited by Al Chara on 2002-08-06 13:21

Thanks,this is a great code for me.
Is it possible to add at this code the following instruction:
I have made it for 999 numbers in place of 9999 numbers.
Now the first number and the last number of a pincode must have a difference of 4
Example: 125 is good
128 is not good 2 and 8 > 4
642 is good
942 is not good 9 and 4 > 4
Is this possible?
Thanks in advance