tricky rand situation

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.
 
B

Bernie Deitrick

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
 
N

Noctos

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
 
B

Bernie Deitrick

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
 
T

Tom Ogilvy

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

? Format(now,"yymmddhhmmss")
040102150108
 
N

Noctos

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
 
T

Tom Ogilvy

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
 
H

Harlan Grove

Tom Ogilvy said:
Why generate a random number. Sounds like all you need is a unique number

? Format(now,"yymmddhhmmss")
040102150108

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.
 
T

Tom Ogilvy

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.
 
H

Harlan Grove

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top