Chnge code help requited

[Guest]

Hi,
I have been given this code to randomly chose a number from range R36:R57
then copy it to cell E34

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

2 changes to the code are required please
1) when a number is selected from the range and pasted to E14 can it be
removed from the original range?
2) If a blank cell is randomly selected, ignore it and loop until a number
is found.

Hope this can be done and thanks in advance

 

[Guest]

Sub getnumber()
mynumber = ""
Do While mynumber = ""
x = Int((57 - 32 + 1) * Rnd + 32)
mynumber = Worksheets("sheet1").Cells(x, 18).Value
Worksheets("sheet1").Cells(x, 18).Clear
Worksheets("sheet1").Cells(34, 5).Value = mynumber
Loop
End Sub


will pick a value randmly from R36:R57 then copy it to cell E34
then do the clear and then loop if necessary

 

[Guest]

thanks for that - just great

Sub getnumber()
mynumber = ""
Do While mynumber = ""
x = Int((57 - 32 + 1) * Rnd + 32)
mynumber = Worksheets("sheet1").Cells(x, 18).Value
Worksheets("sheet1").Cells(x, 18).Clear
Worksheets("sheet1").Cells(34, 5).Value = mynumber
Loop
End Sub


will pick a value randmly from R36:R57 then copy it to cell E34
then do the clear and then loop if necessary