Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number from range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number from range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number from range
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 "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number from range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number from range
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number from range
thank you soooo much !
"Gary''s Student" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number from range
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 "Mike" wrote: 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 "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Addding a Random number to a fixed number..... | Excel Discussion (Misc queries) | |||
How can I match a random number with closest number from sequence? | Excel Worksheet Functions | |||
same number appears in a random number generator | Excel Worksheet Functions | |||
Generating (in a random order)each number once from a given number | Excel Worksheet Functions | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) |