We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

# Tech Helproom

It's free to register, to post a question or to start / join a discussion

# (probably simple) excel random number question

pookie

Likes # 0

Good afternoon,

Excel 2010. Do you know a formula I can use for generating bingo numbers? I want it to randomly draw numbers from 1-89 but it can't randomly select numbers already drawn.

I hope that makes sense.

Thank you.

Simsy

Likes # 0

this formula; =ROUNDUP(RAND()*(89-1)+1,0)

will provide the number(s) you seek, but Excel will generate a new random number each time the sheet recalculates, so I'm not sure how you'd keep track of them, on the sheet, without a macro.

The RAND() function generates a random number between 0 and 1.

Info here, for assistance;

http://www.techonthenet.com/excel/formulas/rand.php

I've incorporated the "roundup" function into what I've suggested to give whole numbers.

Hope this helps.

I'm a bit rusty with macros... see if you can manage with what's above, to be going on with.

Regards,

Simsy

Woolwell

Likes # 0

The problem is excluding duplicate numbers. That isn't "probably simple". You may well need a macro.

Woolwell

Likes # 0

To get a list of 89 numbers in random order:

A1 to A89 Insert function =RAND() B1 to B89 insert function ==RANK(A1,\$A\$1:\$A\$89)

Column B should contain 89 numbers in random order without any duplicates.

pookie

Likes # 0

Thank you for all the replies!

Picklefactory

Likes # 0

I have found some code that will do this (Thanks to Nimrod on MrExcel), do you know anything about how to insert VBA? I'll try and explain, apologies if I over simplify, but I don't know if this is new to you.

I've set up a grid of numbers on the worksheet (Sheet1) to choose from (1-89) in cell range D20:L29 with the number 1 entered in cell D20 then going left to right with 9 in L20 and then consecutive rows continuing down, so you have a solid grid 9 columns x 10 rows with the numbers 1-89 (I left the final cell blank)

It's important you use the specified range for the code to work.

Next, copy the following code to your clipboard. This is tricky to display properly on this forum, as it doesn't display single lines as they are typed, it lumps everything together, so I've had to add a space between each line which you can either ignore or clean up in Excel if you want, but it won't hurt. Ensure you don't miss anything, copy everything from and including Public Sub BingoV2() to the last End Sub.

Public Sub BingoV2()

Randomize

TryAgain:

Rw = Int(((29 - 19) * Rnd) + 20)

Col = Int(((12 - 3) * Rnd) + 4)

With Cells(Rw, Col)

If .Interior.ColorIndex = xlNone Then

Cells(Cells(65536, 1).End(xlUp).Row + 1, 1).Value = .Value

.Interior.ColorIndex = 6

Else

If StillUnusedNumbersV2("D20:L29") Then GoTo TryAgain

End If

End With

End Sub

Public Function StillUnusedNumbersV2(Rng) As Boolean

For Each C In Range(Rng)

If C.Interior.ColorIndex = xlNone Then

StillUnusedNumbersV2 = True

Exit Function

End If

Next C

StillUnusedNumbersV2 = False

MsgBox "All Numbers used", vbInformation, "Game Over"

End Function

Sub Restart()

'

' Restart Macro

'

'

``````Range("D20:L29").Select

With Selection.Interior

.Pattern = xlNone

End With

Range("A2:A93").Select

Selection.ClearContents

Range("A1").Select
``````

End Sub

Open up your VBA window in Excel (Alt+F11). In the left hand explorer window the look for your workbook name in bold (Probably VBA Projects (Book1)). Double click on Sheet1 to open up the main window for that sheet, and in the main window paste the code from above.

Nearly there now, all we have to do is insert a couple of buttons on the worksheet.

So, click back onto your main worksheet where your number grid is, or just close down the VBA window if you want.

On the ribbon, select the Developer tab. (If it is not there, you need to open Excel options via Office button top left corner, select Excel Options, and on the Popular tab, tick the Show Developer tab in the Ribbon check box)

On the Developer tab, Controls section, pull down the menu for Insert and select the top left 'Button' command and click and drag a button to what size and position you want on your sheet, it will immediately open the Macro window to select which macro this button will activate, select Sheet1.BingoV2

Repeat this again for the Reset button, but obviously selecting Sheet1.Restart for the desired Macro for the 2nd button, and Hey Presto, hopefully you should be done.

pookie

Likes # 0

Thank you ever so much! I've give that a go shortly