Solved: Generate numbers 1 - 90 for bingo

[marshybid]

OK, so this may seem loike a slighylt strange request, but her goes.

The kids want to play bingo, how can I create a button thast each time pressed will generate a random number between 1 and 90, continue to generate each time pressed not using any numbers already generated???

Then have a reset button to start again??

Marshybid

[Simon Lloyd]

go here http://www.jcaliff.net/japan/jetres.htm scroll to bottom of page

[Bob Phillips]

Okay this is one way, but you need to be sitting down, and you need to watch very carefully.



First, ensure cells A1:A90 are empty and then goto Tools>Options and on the Calculation tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A1="")+(AND(B1>0,COUNTIF($B$1:$B$90,B1)=1)),B1,INT(RAND()*90+1))
it should show a 0.

In C1, enter =IF(COUNTIF($B$1:$B$90,ROW(A1)),ROW(A1),"")

Copy B1:C1 down to B90:C90..

To run it, create a button that puts an x in the next empty cell in A1:A90. This wwill generate a random number in the B cell next to that A cll which won't change.

You can pick up the latest value in B as the next number. This gives you a nice list of numbers generated in column C as a check-back.

To re-starta re-calculation, clear cells A1:A90, edit cell B1, don't change it, just edit to reset to 0, copy B1 down to B90.

[Bob Phillips]

go here http://www.jcaliff.net/japan/jetres.htm scroll to bottom of page

However did you find that?

[mikerickson]

This combiation of routines might help.

Public ArrayOfNinety As Variant

Sub showNextNumberOf90()
Static showMe As Long
If showMe < 1 Or 90 < showMe Then
Call reset90Array
showMe = 1
End If
MsgBox ArrayOfNinety(showMe)
showMe = showMe + 1
End Sub

Sub reset90Array()
Dim i As Long, randIndex As Long, temp As Long
If IsEmpty(ArrayOfNinety) Then
ReDim ArrayOfNinety(1 To 90)
For i = 1 To 90
ArrayOfNinety(i) = i
Next i
End If
For i = 1 To 90
Randomize
randIndex = Int(90 * (Rnd())) + 1
temp = ArrayOfNinety(i)
ArrayOfNinety(i) = ArrayOfNinety(randIndex)
ArrayOfNinety(randIndex) = temp
Next i
Rem Range("a1:a90").Value = Application.Transpose(ArrayOfNinety):Rem test line
MsgBox "Reset"
End Sub

[Simon Lloyd]

However did you find that?Something i found quite a while ago whilst trying the same thing for my youngest!

[david000]

I remembered this episode, because I'd never used the DataSeries property before.


Sub StartOver() '<<<<<<<<<<<<<<< one button
Cells.ClearContents
Range("a1") = "Your Numbers"
Range("e1") = "Number"
Range("f1") = "Sort"
Range("e2") = 1
Range("e2:e91").DataSeries step:=1
Range("f2:f91").Formula = "=RAND()"
End Sub

Sub DrawUnique() '<<<<<<<<<<<<<<<<<<<<<<<<<<<< second button
Range("f1").CurrentRegion.Sort key1:=Range("f1"), Header:=xlYes
Range("e100").End(xlUp).Copy _
Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Range("e100").End(xlUp).Resize(1, 2).ClearContents
End Sub

[ndendrinos]

Here Bingo is played with 75 numbers ... maybe you can adjust it to 90.
Have fun.

[marshybid]

Wow, what a great response from all of you.

Followed all of the links and was able to pull something together by using bits of each.

xld, the formula approach worked fine too.... Definitely don't fully understand how it works, but it does.

Thanks to all

Marshybid :hi:

[Bob Phillips]

I just couldn't do it with boring old VBA, too easy.

Here is the formula approach in action

[marshybid]

I just couldn't do it with boring old VBA, too easy.

Here is the formula approach in action
OK, that looks really good. I used your formula approach, but I didn't add the table and the number box in green etc :bow: :bow: :bow:

Amazing, the kids will love it, and I might get to watch Moto GP in peace one Sunday now

Marshybid :beerchug: