Page 1 of 2 12 LastLast
Results 1 to 10 of 15

4 digit random pin number

This is a discussion on 4 digit random pin number within the Excel Questions forums, part of the Question Forums category; I'm new to some of the more advanced features to Excel and trying to figure this out. I'm trying to ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    6

    Default

    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.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    Welcome to the Board, Pucky.

    Will you be storing the used PIN numbers in a worksheet?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Aug 2002
    Posts
    6

    Default

    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.

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    Try the following:
    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

    This will place unique numbers in column A. It also assumes that there is a heading in Cell A1.

    _________________
    Kind regards,

    ALCHARA

    EDIT: Fixed part of the code. Had the variable name wrong.

    [ This Message was edited by: Al Chara on 2002-08-06 13:21 ]

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    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.
    Bye,
    Jay

  6. #6
    New Member
    Join Date
    Aug 2002
    Posts
    6

    Default

    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)

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    Maybe you have to add the worksheet name.
    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


    _________________
    Kind regards,

    ALCHARA
    [ This Message was edited by: Al Chara on 2002-08-06 14:00 ]

  8. #8
    New Member
    Join Date
    Aug 2002
    Posts
    6

    Default

    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

  9. #9
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701

    Default

    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.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  10. #10
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default

    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....microsoft.com

    For a programmatic solution, look at
    http://groups.google.com/groups?selm....microsoft.com

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com