randomly entering a number from a specific set

[srichelieu]

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

 

[agihcam]

start a blank excel and try;

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]

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]

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]

Thank you very much!

 

[srichelieu]

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]

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]

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]

not so sure but try;

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

 

[srichelieu]

Thanks...I've nearly got it working for what I need.

very much appreciated!