4 digit random pin number

pucky900

New Member
Joined
Aug 5, 2002
Messages
6
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Welcome to the Board, Pucky.

Will you be storing the used PIN numbers in a worksheet?
 
Upvote 0
Yes, they will be stored in a worksheet. Right now I have a list of first & last names and I'm trying to create a unique pin in a column for each name. Later this going to be place into either Access or MS SQL for a web login.
 
Upvote 0
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"><sup><span style="text-decoration: overline">AL</span></sup><u><sub>CHARA</sub></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
 
Upvote 0
Hi Al,

Good job. Very nice.

One thing -- Match in VBA is extremely quirky.

Application.WorksheetFunction.Match()
and
WorksheetFunction.Match

don't always works as expected.

Application.Match is more stable.

No explanation for this, as all should work equally, but they definitely do not.

Change the Test variable line to

Test = Application.Match(RndNo, Rng.Address, 0)

It make not make a difference in this specific instance, as you want the error, but keep this in mind.

BTW, I have already stored this code away for future use. Excellent work.
 
Upvote 0
I created a macro using your code, but I keep getting a "Run-time error "10041". Method "End" of Object "Range"" This is where I get the error.

Set LstCell = Range("A65536").End(x1Up).Offset(1, 0)
 
Upvote 0
Maybe you have to add the worksheet name.<pre>Sub PINCreate()

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

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

End Sub</pre>

_________________
Kind regards,<font size="5"><sup><span style="text-decoration: overline">AL</span></sup><u><sub>CHARA</sub></u></font
This message was edited by Al Chara on 2002-08-06 14:00
 
Upvote 0
I'm still getting the same error in the same location. I was wondering if it had to do with the number of rows because when I open up a new workbook, it opens up with 64 rows. I did try changing the range to "A64", but no go. Here is my code that I typed in:

Sub PINCreate()

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

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

End Sub
 
Upvote 0
On 2002-08-06 13:48, pucky900 wrote:
I created a macro using your code, but I keep getting a "Run-time error "10041". Method "End" of Object "Range"" This is where I get the error.

Set LstCell = Range("A65536").End(x1Up).Offset(1, 0)
Somehow you have (x1Up) instead of (xlUp). Just copy and paste my code into your module.
 
Upvote 0
If the intent is to assign random codes to new people (the names mentioned in a post), and if the eventual intent is to put the information in a database, it would be significantly faster to create the random numbers up front, and then assign one to each new person.

There are two performance issues with generating a number and then checking if it is acceptable.

First, the 10,000th number will *on average* require 10,000 random numbers before an acceptable one is found -- while, actually, it is not random at all!

Second, trying to do the equivalent of a MATCH on a database table might not be the smartest thing to do -- performance wise that is (unless, of course, the PIN is used to create an index table!).

For a non-programmatic solution, look at
http://groups.google.com/groups?selm=MPG.1689584b91ef5e5298a3d4@msnews.microsoft.com

For a programmatic solution, look at
http://groups.google.com/groups?selm=MPG.178ba7a1b7d9b10898a684@msnews.microsoft.com

In both cases, you will have to adapt the solution(s) to your specific needs.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top