Help with creating a unique set of numbers..

  • Thread starter Thread starter Grunta
  • Start date Start date
G

Grunta

Hello

I would like a solution regard to excel formulas / VBA scripting.

The purpose is to assist in generatating unique PIN numbers for users
on our phone system..

The numbers generated need to be a number between 1000 & 9999...

The basic formula I have used (=RANDBETWEEN(1000,9999)) does it
intitally, but if the formula gets extended or a row gets deleted the
numbers are regenerated....

The numbers need to remain how they are at the initial time of running
the formula..

Any ideas? – I guess there needs to be some fault tolerances involved,
but for the life of me I would know what they are....

Kind Regards,

Grant
 
Grant,

Run this macro once for each unique PIN you want, generated at the bottom of
column A on Sheet1, which you can change as needed.

HTH,
Bernie
MS Excel MVP


Sub RandPIN()
Dim myNum As Integer
Dim FindNum As Boolean
Dim myR As Range
Set myR = Worksheets("Sheet1").Range("A:A")

FindNum = True
While FindNum
myNum = 1000 + Int(Rnd() * 8999)
If IsError(Application.Match(myNum, myR, False)) Then
Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2).Value = myNum
FindNum = False
End If
Wend
End Sub

Hello

I would like a solution regard to excel formulas / VBA scripting.

The purpose is to assist in generatating unique PIN numbers for users
on our phone system..

The numbers generated need to be a number between 1000 & 9999...

The basic formula I have used (=RANDBETWEEN(1000,9999)) does it
intitally, but if the formula gets extended or a row gets deleted the
numbers are regenerated....

The numbers need to remain how they are at the initial time of running
the formula..

Any ideas? – I guess there needs to be some fault tolerances involved,
but for the life of me I would know what they are....

Kind Regards,

Grant
 
My free "Special Randoms" Excel workbook might be of help.
Download from... http://excelusergroup.org/media/
(no registration required, no advertising, no spam)
--
Jim Cone
Portland, Oregon USA




"Grunta"
<[email protected]>
wrote in message
Hello
I would like a solution regard to excel formulas / VBA scripting.
The purpose is to assist in generatating unique PIN numbers for users
on our phone system..

The numbers generated need to be a number between 1000 & 9999...

The basic formula I have used (=RANDBETWEEN(1000,9999)) does it
intitally, but if the formula gets extended or a row gets deleted the
numbers are regenerated....

The numbers need to remain how they are at the initial time of running
the formula..

Any ideas? – I guess there needs to be some fault tolerances involved,
but for the life of me I would know what they are....

Kind Regards,

Grant
 
Back
Top