PDA

View Full Version : [SOLVED] Lucky number code



koala
07-29-2006, 04:41 AM
Is anyone ablt to assist with some code.

I have a list of numbers, in Column A starting at row 100, The amount of numbers is variable.

I want excel to select a random number from the range A100:A?? and place this value in cell A1, then repeat for 5 seconds. (which I hope will give a visual effect of the numbers spinning for 5 seconds and then stopping and displaying a lucky number.)

Is there any simple way for a macro to do this?

Hoping someone can assist

Koala

Bob Phillips
07-29-2006, 05:02 AM
Dim nTime As Double

Sub Test()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Next i
End Sub

Sub LuckyNumber()
Dim i As Long
Dim iLastRow As Long
Dim nRandom As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
nRandom = Int(Rnd * iLastRow - 99 + 1)
Range("A1") = Application.Index(Columns(1), nRandom + 99)
nTime = Now + TimeSerial(0, 0, 5)
Application.OnTime nTime, "LuckyNumber"
End Sub

Sub StopLoop()
Application.OnTime nTime, "LuckyNumber", , False
End Sub

koala
07-29-2006, 05:46 AM
XLD,

Thank you for your quick reply.

I have enclosed a worksheet with your code, as it doesnt quite work how I needed it too, which is probably due to me not clearly identifying what I needed.

I need the number in A1 to be a static display, then when I press the spin button, the number in A1 will rapidly change for 5 seconds, then become static again.

I need it this way so that A1 gives the appearance of the numbers spinning before a lucky number is displayed.

cheers
Koala

Bob Phillips
07-29-2006, 07:11 AM
Try this code


Sub LuckyNumber()
Dim nTime As Double
Dim i As Long
Dim iLastRow As Long
Dim nRandom As Long
nTime = Now + TimeSerial(0, 0, 5)
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Do
nRandom = Int(Rnd * iLastRow - 99 + 1)
Range("A1") = Application.Index(Columns(1), nRandom + 99)
Loop While Now < nTime
End Sub

koala
07-29-2006, 07:46 AM
Thank you xld,

That works perfect. You are a legend.

I will mark it as solved.

cheers
Koala

lucas
07-29-2006, 07:56 AM
Bob, I am amazed by how quickly you crank out good code. Nice one