Random number from range

[Guest]

Hi,
can somebody give some code or a formula to chose a number at random from a
list in range R32:R52, then place this randomly chosen number in cell E14 of
sheet3 and finally remove this number from the list in the original range.

thanks

 

[Guest]

Assuming the list is in Sheet1:


Sub marine()
i = Round(Rnd() * 20, 0)
Set r = Sheets("sheet1").Range("R32").Offset(i, 0)
Sheets("Sheet3").Range("E14").Value = r.Value
r.Clear
End Sub

 

[Guest]

This works by randomely choosing the row nimber to get the mimber from and
then writing whatever was found in that cell to E14

Mike

Sub getnumber()
x = Int((52 - 32 + 1) * Rnd + 32)
mynumber = Worksheets("sheet1").Cells(x, 18).Value
Worksheets("sheet1").Cells(14, 4).Value = mynumber
End Sub

Mike

 

[Guest]

Great - thanks,
just one other question,
can you change this code to only chose a cell from the range (R32:R52) if it
has a value in it.
eg if R30 has the number 100 in it - fine place this in E14 (sheet3) and
remove from list, if R30 is blank then chose another
is this possible?
thanks again

 

[Guest]

Sub marine()
v = 0
Do Until v <> 0
i = Int(Rnd() * 21)
Set r1 = Sheets("sheet1").Range("R32").Offset(i, 0)
Set r3 = Sheets("Sheet3").Range("E14")
v = r1.Value
r3.Value = v
r1.Clear
Loop
End Sub


Once the last value has been taken, it will loop forever.

 

[Guest]

thank you soooo much !

Sub marine()
v = 0
Do Until v <> 0
i = Int(Rnd() * 21)
Set r1 = Sheets("sheet1").Range("R32").Offset(i, 0)
Set r3 = Sheets("Sheet3").Range("E14")
v = r1.Value
r3.Value = v
r1.Clear
Loop
End Sub


Once the last value has been taken, it will loop forever.

 

[Guest]

many thanks - that is just great - last question (I hope)
can you suggest how to chnage it so that if the selected random cell value
is between 0.99 and 751 then the formatt of cell E14 (sheet3) should be
Code:
Selection.Interior.ColorIndex = 41
Selection.Font.ColorIndex = 2

if it falls between 999 and 250001 then it should be
Code:
Selection.Interior.ColorIndex = 3
Selection.Font.ColorIndex = 2


...any ideas if this possible