Random Number for Drawing

[Dan Tschippert]

I am trying to make a spreadsheet that would randomly select and record a 3
digit number on demand by pressing an F key or clicking on a button.

I want to use this for drawings to replace a lottery type ball machine.

I need it to keep track of the time the number was generated and the number
and not recalculate the numbers at all. It would just develop a "list of
numbers" either at predetermined time intervals or when I would press a key
or click a button.

So I would have a column for the drawing number, the date and time, the
number drawn which would be written every time I executed the "drawing"
command.

Anyone have any ideas? I did poke around and found random number generators,
and they seem to come close, but aren't exactly want I need.

Thanks

Dan

 

[Bernie Deitrick]

Dan,

You could use the macro below to place the numbers in column A, with the
date/time stamp in column B. You could put a drawing object on your
worksheet, and assign the macro to it.

HTH,
Bernie
MS Excel MVP

Sub DanRandom()
Dim myCell As Range
Set myCell = Range("A65536").End(xlUp)(2)

myCell.Value = "'" & Format(1000 * Rnd(), "000")
With myCell(1, 2)
.Value = Now()
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
End With

End Sub

 

[Dan Tschippert]

Bernie, thanks for your response. I tried what you said might work, but the
code seems to be wrong in line 3. Could you double check for me.

Thanks

Dan

 

[Bernie Deitrick]

Dan,

What error and/or error message are you getting? The code worked fine for
me.

HTH,
Bernie
MS Excel MVP

 

[Dan Tschippert]

Bernie,
I'm sorry, I got it working. I missed the ' between the " ".

Is there a way to copy the last number into another cell? I want to make a
merged text field with a bigger font showing the last number drawn, so it
would change every time I pressed the drawing object to run the macro.

Dan

 

[Bernie Deitrick]

Dan,

If your list of numbers is in column A, and your header is in row 1 (with no
blank cells belwo it) then simply:

=INDEX(A:A,COUNTA(A:A),1)

Otherwise, you can use

=INDEX(A:A,COUNTA(A:A) + "Number of blank cells above your list",1)

So, if there is one blank cell above your list:
=INDEX(A:A,COUNTA(A:A) + 1,1)

HTH,
Bernie
MS Excel MVP