Any ideas why this code is not working - i am a complete
novice to VBA!!!

It should generate a random number and then check if that
numbers is unique and if it is not loop until it is.

Please help - this is causing me to lose hair!!


randball1: Range("Ball1") = Int(Rnd() * 49) + 1
randball2: Range("Ball2") = Int(Rnd() * 49) + 1
If Range("Ball2") = Range("Ball1") Then GoTo randball2
randball3: Range("Ball3") = Int(Rnd() * 49) + 1
If Range("Ball3") = Range("Ball1") Or Range("Ball2") Then
GoTo randball3
randball4: Range("Ball4") = Int(Rnd() * 49) + 1
If Range("Ball4") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Then GoTo randball4
randball5: Range("Ball5") = Int(Rnd() * 49) + 1
If Range("Ball5") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Or Range("ball4") Then GoTo randball5
randball6: Range("Ball6") = Int(Rnd() * 49) + 1
If Range("Ball6") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Or Range("Ball4") Or Range("ball5") Then
GoTo randball6

Hi,

a)
to get real random numbers use
Randomize
at the beginning of your code and then always use
Rnd()^Rnd()
then you can multiply the random numer with 1000000 and make an integer.

b)
you can create a list of random numbers using a). If you need 100 numbers
let it be 100 rows long, write all numbers form 1 to 100 in the next column
and sort the 2 columns by the colum with random numbers. Then you will have
numbers from 1 to 100 in a random way and every number only occurs once.

regards

arno

"Samual" schrieb im Newsbeitrag
...
Any ideas why this code is not working - i am a complete
novice to VBA!!!

It should generate a random number and then check if that
numbers is unique and if it is not loop until it is.

Please help - this is causing me to lose hair!!


randball1: Range("Ball1") = Int(Rnd() * 49) + 1
randball2: Range("Ball2") = Int(Rnd() * 49) + 1
If Range("Ball2") = Range("Ball1") Then GoTo randball2
randball3: Range("Ball3") = Int(Rnd() * 49) + 1
If Range("Ball3") = Range("Ball1") Or Range("Ball2") Then
GoTo randball3
randball4: Range("Ball4") = Int(Rnd() * 49) + 1
If Range("Ball4") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Then GoTo randball4
randball5: Range("Ball5") = Int(Rnd() * 49) + 1
If Range("Ball5") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Or Range("ball4") Then GoTo randball5
randball6: Range("Ball6") = Int(Rnd() * 49) + 1
If Range("Ball6") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Or Range("Ball4") Or Range("ball5") Then
GoTo randball6

Sub RandBall()
For i = 1 To 6
Range("Ball" & i).ClearContents
Next
randball1: Range("Ball1") = Int(Rnd() * 49) + 1
randball2: Range("Ball2") = Int(Rnd() * 49) + 1
If Range("Ball2") = Range("Ball1") Then _
GoTo randball2
randball3: Range("Ball3") = Int(Rnd() * 49) + 1
If Range("Ball3") = Range("Ball1") Or _
Range("Ball3") = Range("Ball2") Then _
GoTo randball3
randball4: Range("Ball4") = Int(Rnd() * 49) + 1
If Range("Ball4") = Range("Ball1") Or _
Range("Ball4") = Range("Ball2") Or _
Range("Ball4") = Range("Ball3") Then _
GoTo randball4
randball5: Range("Ball5") = Int(Rnd() * 49) + 1
If Range("Ball5") = Range("Ball1") Or _
Range("Ball5") = Range("Ball2") Or _
Range("Ball5") = Range("Ball3") Or _
Range("Ball5") = Range("ball4") Then _
GoTo randball5
randball6: Range("Ball6") = Int(Rnd() * 49) + 1
If Range("Ball6") = Range("Ball1") Or _
Range("Ball6") = Range("Ball2") Or _
Range("Ball6") = Range("Ball3") Or _
Range("Ball6") = Range("Ball4") Or _
Range("Ball6") = Range("ball5") Then _
GoTo randball6

End Sub

--
Regards,
Tom Ogilvy

"Samual" wrote in message
...
Any ideas why this code is not working - i am a complete
novice to VBA!!!

It should generate a random number and then check if that
numbers is unique and if it is not loop until it is.

Please help - this is causing me to lose hair!!


randball1: Range("Ball1") = Int(Rnd() * 49) + 1
randball2: Range("Ball2") = Int(Rnd() * 49) + 1
If Range("Ball2") = Range("Ball1") Then GoTo randball2
randball3: Range("Ball3") = Int(Rnd() * 49) + 1
If Range("Ball3") = Range("Ball1") Or Range("Ball2") Then
GoTo randball3
randball4: Range("Ball4") = Int(Rnd() * 49) + 1
If Range("Ball4") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Then GoTo randball4
randball5: Range("Ball5") = Int(Rnd() * 49) + 1
If Range("Ball5") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Or Range("ball4") Then GoTo randball5
randball6: Range("Ball6") = Int(Rnd() * 49) + 1
If Range("Ball6") = Range("Ball1") Or Range("Ball2") Or
Range("Ball3") Or Range("Ball4") Or Range("ball5") Then
GoTo randball6

If you haven't already, look at help for RANDOMIZE. Also, I've found
some very good (and free) add-ins from NumTech (specifically NtRand)
if you wish to create something from scratch. However, like the
Christmas song this has been done "many times, many ways". Do a
Google search on "Return random numbers using VBA in Microsoft Excel"
and look at the first hit. There is code to generate unique random
#'s you can cut, paste, and modify.

Regards,
Steve Hieb