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.
--
Gary''s Student
gsnu200709


"Anthony" wrote:

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

"Gary''s Student" wrote:

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

--
Gary''s Student
gsnu200709


"Anthony" wrote:

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