randomly entering a number from a specific set

srichelieu

New Member
Joined
Mar 28, 2005
Messages
8
Hello,

I am trying to figure out how to have excel randomly return a value in a cell from a specific set of numbers...

basically i would like for excel to randomly enter a 30, 60, 90, 120, 150, 180, 210, 240, 270, or 300 value in a series of cells (say a1 through a 400). In addition, once it is done the first time, I don't want it to change again.

during a web search I saw the suggestion for something like this:
=CHOOSE(INT(RAND()*4)+1,3,6,8,15)
but this didn't work and gave me a #Value error

any help is much appreciated

Steph
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
start a blank excel and try;
Code:
Sub putrand()
[a1] = 30
[a2] = 60
Range("A1:A2").AutoFill Destination:=Range("A1:A400"), Type:=xlFillDefault
Range("B1").FormulaR1C1 = "=RAND()"
Range("b1").AutoFill Destination:=Range("b1:b400"), Type:=xlFillDefault
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("b").Delete
End Sub
 

srichelieu

New Member
Joined
Mar 28, 2005
Messages
8
Thanks for the response but I'm not familiar with how to enter code into excel. Could you point me in the right direction so that i can research how to do it and try it?
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
hit Alt + F11, this will open VBE
in the left side, you will find VBAProject....
right click to it > Insert > Module then paste the code in the large white screen to the right
close the VBE using x button or hit Alt + Q

click on Tools > Macro > Macros and double click the putrand macro
hope it helps.
 

srichelieu

New Member
Joined
Mar 28, 2005
Messages
8

ADVERTISEMENT

Thank you very much!
 

srichelieu

New Member
Joined
Mar 28, 2005
Messages
8
OK. I got the code in and it did fill cells a1 to a400 but it did so with random numbers not just a 30 or 60. any other ideas??
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624

ADVERTISEMENT

OK. I got the code in and it did fill cells a1 to a400 but it did so with random numbers not just a 30 or 60. any other ideas??
what do you mean? i thought you want it in random.
or do you want just to display in a1:a400 the number start from 30,60,90 and so on?
 

srichelieu

New Member
Joined
Mar 28, 2005
Messages
8
in cells a1:a400 i want to display one of the following numbers - 30, 60, 90, 120, 150, 180... in no particular order or pattern. the result could thus look something like:

a1 - 60
a2 - 120
a3 - 60
a4 - 30
a5 - 180
...

make sense?
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
not so sure but try;
Code:
Sub putrand()
[a1] = 30
[a2] = 60
Range("A1:A2").AutoFill Destination:=Range("A1:A10"), Type:=xlFillDefault
Range("A1:A10").Copy
Range("A11:A400").Select
ActiveSheet.Paste
Range("B1").FormulaR1C1 = "=RAND()"
Range("b1").AutoFill Destination:=Range("b1:b400"), Type:=xlFillDefault
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("b").Delete
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,038
Messages
5,545,672
Members
410,697
Latest member
srishtijain0708
Top