Solved: Why the blanks?

[ndendrinos]

3651 Here is a macro that picks 6 random numbers from 1 to 49 .
It might pick doubles and while I wish to address this the immediate problem is this: Why does it pick "out of th range" ? for I get blanks as you problably will
if you run it enough times.
Thank you.

[ndendrinos]

Moved the range to A1:A49 and left the code as is and it works ... not sure why or how but I ran it several times and got no "blanks"
Still getting doubles ... can you help with this problem maybe?
Thank you

[jungix]

What are you trying to do exactly. Picking 6 numbers or 49? You're trying to do something like a random loto: 6 numbers out of 49 without doubles?

[Bob Phillips]

Private iLastRow As Long
Const nSecs As Long = 3

Sub one()
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
GetRandomNumber Range("B1")
End Sub
Sub two()
GetRandomNumber Range("C1")
End Sub
Sub three()
GetRandomNumber Range("D1")
End Sub
Sub four()
GetRandomNumber Range("E1")
End Sub
Sub five()
GetRandomNumber Range("F1")
End Sub
Sub six()
GetRandomNumber Range("G1")
End Sub

Private Sub GetRandomNumber(ByRef rng As Range)
Dim nTime As Double
Dim nRandom As Long

nTime = Now + TimeSerial(0, 0, nSecs)
Do
nRandom = Int(Rnd * (iLastRow - 99) + 1)
rng.Value = Application.Index(Columns(1), nRandom + 99)
Loop While Now < nTime
End Sub

[Bob Phillips]

To fix duplicates problem



Private iLastRow As Long
Private aryUsed
Const nSecs As Long = 5

Sub one()
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
GetRandomNumber Range("B1"), True
End Sub
Sub two()
GetRandomNumber Range("C1")
End Sub
Sub three()
GetRandomNumber Range("D1")
End Sub
Sub four()
GetRandomNumber Range("E1")
End Sub
Sub five()
GetRandomNumber Range("F1")
End Sub
Sub six()
GetRandomNumber Range("G1")
End Sub

Private Sub GetRandomNumber(ByRef rng As Range, Optional First As Boolean = False)
Dim nTime As Double
Dim nRandom As Long

nTime = Now + TimeSerial(0, 0, nSecs)
Do
nRandom = Int(Rnd * (iLastRow - 99) + 1)
rng.Value = Application.Index(Columns(1), nRandom + 99)
Loop While Now < nTime

If First Then
ReDim aryUsed(1 To 1)
aryUsed(1) = rng.Value
Else
If IsError(Application.Match(rng.Value, aryUsed, 0)) Then
ReDim Preserve aryUsed(1 To UBound(aryUsed) + 1)
aryUsed(UBound(aryUsed)) = rng.Value
Else
GetRandomNumber rng
End If
End If
End Sub

[ndendrinos]

Thank you both.
Jungix you are correct, this is what I'm doing

Xld I assume that your first solution fixes the problems with the blanks.
Maybe you've work on it before you had a chance to read my second post .
I got rid of the problem by moving the range of numbers to A1:A49
Should I move back the range to what it was (starting @ A:98 ?)

I will test the solution you offer to the duplicates problem and post back
Again thank you

[Bob Phillips]

Thank you both.
Jungix you are correct, this is what I'm doing

Xld I assume that your first solution fixes the problems with the blanks.
Maybe you've work on it before you had a chance to read my second post .
I got rid of the problem by moving the range of numbers to A1:A49
Should I move back the range to what it was (starting @ A:98 ?)

I will test the solution you offer to the duplicates problem and post back
Again thank you

Yes, that is right. You can move it back because your error was in subtracting 99 from iLastrow, except you wern't, you were subtracting 99 from the random number * ilastrow. Judicious use of brackets solved it. The second stops any chance of duplicates.

[ndendrinos]

XLD works great ... run it about fifty times and no dups ... number 49 came up but still no number 1 ... I'll keep running it until I see it

One more thing ... I notice that a number will spin longer that others , is this because i "hits" a duplicate and thus spins some more ?

Thank you very much for your help

[Bob Phillips]

XLD works great ... run it about fifty times and no dups ... number 49 came up but still no number 1 ... I'll keep running it until I see it

Logic error, change



nRandom = Int(Rnd * (iLastRow - 99) + 1)


to



nRandom = Int(Rnd * (iLastRow - 99)) + 1



One more thing ... I notice that a number will spin longer that others , is this because i "hits" a duplicate and thus spins some more ?

Exactly, when it hits a duplicate, I re-run the generator.

[ndendrinos]

XLD, maybe I misundertood you .. I've replaced :
nRandom = Int(Rnd * (iLastRow - 99) + 1 with
nRandom = Int(Rnd * (iLastRow - 99)) + 1 and got an error ...
"Compile error/Syntax error"

[jungix]

Before it was nRandom = Int(Rnd * (iLastRow - 99) + 1)
so don't forget to remove the last parenthese at the end.

[Bob Phillips]

You shouldn't have got an error, but try this proecedure, I have tested it and seen 1 and 49



Private Sub GetRandomNumber(ByRef rng As Range, Optional First As Boolean = False)
Dim nTime As Double
Dim nRandom As Long

nTime = Now + TimeSerial(0, 0, nSecs)
Do
nRandom = Int(Rnd * (iLastRow - 97)) + 1
rng.Value = Application.Index(Columns(1), nRandom + 97)
Loop While Now < nTime

If First Then
ReDim aryUsed(1 To 1)
aryUsed(1) = rng.Value
Else
If IsError(Application.Match(rng.Value, aryUsed, 0)) Then
ReDim Preserve aryUsed(1 To UBound(aryUsed) + 1)
aryUsed(UBound(aryUsed)) = rng.Value
Else
GetRandomNumber rng
End If
End If
End Sub

[mdmackillop]

A different methodology

Sub PickWinners()
Dim ws As Worksheet
Randomize
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets.Add
Range("A1:A49").Formula = "=RAND()"
Range("B1") = 1
Range("B1:B49").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
Range("A1:B49").Sort Key1:=Range("A1"), Order1:=xlAscending
Range("B1:B6").Copy
ws.Range("B1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
ActiveSheet.Delete
Range("A1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set ws = Nothing
End Sub

[jungix]

Wow that's really clever mdmackillop. Nice trick ;)

Is it possible that 2 of the rands have the same value? The probability of such an event is theoretically 0, but in practice Excel must truncate its numbers so maybe there's a chance (out of billions) that 2 of them are might be identical

[Bob Phillips]

A different methodology

Sub PickWinners()
Dim ws As Worksheet
Randomize
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets.Add
Range("A1:A49").Formula = "=RAND()"
Range("B1") = 1
Range("B1:B49").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
Range("A1:B49").Sort Key1:=Range("A1"), Order1:=xlAscending
Range("B1:B6").Copy
ws.Range("B1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
ActiveSheet.Delete
Range("A1").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set ws = Nothing
End Sub



Malcolm,

If you had seen the OPs previous post, you would know he wanted this spinning effect, to simulate a tumber. Otherwise I would have just used RANDBETWEEN(1,49) :yes.

[ndendrinos]

3654Thank you Jungix ... fixed that one.
Trying to finally see the number 1 somewhere I've started reducinf the numbers from 1-49 to less and again less until 1-10 and still no 1 nor 2 .. not sure how to interpret this but there must something wrong in the range
Here is the example.

[mdmackillop]

Malcolm,

If you had seen the OPs previous post, you would know he wanted this spinning effect, to simulate a tumber. Otherwise I would have just used RANDBETWEEN(1,49) :yes.

Oops!
That will teach me not to jump in without reading all the posts!

Jungix,
Duplicate randoms don't matter for something like this, as long as Excel orders the sort.

[ndendrinos]

XLD still with your first solution

nRandom = Int(Rnd * (iLastRow - 97)) + 1
rng.Value = Application.Index(Columns(1), nRandom + 97)

instead of:

nRandom = Int(Rnd * (iLastRow - 99)) + 1
rng.Value = Application.Index(Columns(1), nRandom + 99)

does it for me ....

I will also try your second code as well as Malcom's.
To all of you thanks ,, you might have made a millionaire out of me... the 32 million draw is Wednesday.

Best regards,
Nick

Unless any of you adds to this thread I will come back later on and mark it "Solved"

[jungix]

You're right mdmackillop. Only in the case where the 6th and 7th are the same would it make a difference. Then the lower number would be kept, and thus 1 should have a (very very very slightly) higher probability to appear than 49. But this imprecision is probably even smaller than the rand method of Excel, which is not a perfect random.