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

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

--
Gary''s Student
gsnu200710


"Anthony" wrote:

> 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

thanks for that - just great

"Gary''s Student" wrote:

> 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
>
> --
> Gary''s Student
> gsnu200710
>
>
> "Anthony" wrote:
>
> > 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