tricky rand situation

  • Thread starter Thread starter Noctos
  • Start date Start date
N

Noctos

i am using the rand function to generate a number which is to be given
to each customer who fills in my form. Their number and details will be
placed in a look up table. How could i prevent the same number coming
up out of the 10,000,000 possible outcomes when i multiply the rand
function by 10,000,000.
 
Noctos,

Check the initial result against your record table: if you find the
value, generate another random number, and continue until you don't
find it in the table. If you use the MATCH function with the last
parameter = FALSE (to find exact matches), it will return an error
when you have a unique number.

HTH,
Bernie
MS Excel MVP
 
yea the thing is the user will be doing this by thereselves so i won't
be there to keep on checking if the rand number to do. I think the best
way is to use vba. The rand number will be generated and this number
can be compared to the table using the match value if a value is found
then the rand number could be refreshed like a loop until a unique
value is found and this value is then displayed. Does anyone know how
to translate this into vba code
 
Noctos,

The sub below will store the unique number appearing in cell A1 in
column B of the same sheet.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim boolMatch As Boolean
boolMatch = True

While boolMatch
Range("A1").Value = "'" & Format(Rnd() * 10000000, "0000000")
If Application.WorksheetFunction.CountIf(Range("B:B"),
Range("A1").Value) = 0 Then
boolMatch = False
Range("B65536").End(xlUp)(2).Value = "'" & Range("A1").Text
End If
Wend

End Sub
 
Why generate a random number. Sounds like all you need is a unique number

? Format(now,"yymmddhhmmss")
040102150108
 
not entirely sure how to use your unique number tom but bernie vb is
saying there is an error in these two lines

If Application.WorksheetFunction.CountIf(Range("B:B"),
Range("A1").Value) = 0 Then
 
You are a victim of email wordwrap. It should all be one line, or put in a
line continuation character (space underscore):

If Application.WorksheetFunction.CountIf(Range("B:B"), _
Range("A1").Value) = 0 Then
 
Tom Ogilvy said:
Why generate a random number. Sounds like all you need is a unique number

? Format(now,"yymmddhhmmss")
040102150108
Click to expand...

While it may be rare, this technique can cause problems if multiple users
could be using different copies of the workbook (or its forms)
simultaneously. The problem would be similar to generating unique invoice
numbers when there are multiple simultaneous users. If that were so, then
some form or centralized generation of unique numbers is unavoidable.
 
Assuming a single user per customer
Easily solved by appending some unique identification information for the
customer.

All schemes would be made more robust by using a central issuing authority,
but that didn't appear to be the intent of the OP.
 
Tom Ogilvy said:
All schemes would be made more robust by using a central issuing authority,
but that didn't appear to be the intent of the OP.
Click to expand...
....

Perhaps. In my own experience, unique ID generation is highly susceptible to
Murphy's Law.
 
Back
Top