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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

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
 

Forum statistics

Threads
1,141,665
Messages
5,707,699
Members
421,524
Latest member
Bharath99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top