# Creating a Random Number Generator

#### neonexistence

##### New Member
Hi there,

I am a high school teacher and am looking at creating an excel sheet that will act as a random number generator. As a start, I would like the code to randomly generate a number from a list of values and record it in column 1. I would then like it to continue recording each trial underneath in column 1.

Some extensions of this (not necessary but helpful) are:

1. The probability of each outcome is not equal (sum of rolling two dice)

2. Can quickly run x amount of trials (1-1000)

I am going to be putting a frequency table next to it, so the macro would not need to count the number of 1's, 2's etc. but merely copy them down in a big list.

Any feedback on how to achieve this would be greatly appreciated!

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### diddi

##### Well-known Member
i was a chalkie too once, but i saw the light!

heres sample... The values from which your randomly chosen number is selected is Col E. C1 is the number of trials (selections) rolls etc

and heres the code
VBA Code:
``````Sub PickRandomFromE()
Dim Trial As Long, NumTrials As Long, LastRow As Long
NumTrials = Cells(1, 3)
LastRow = Cells(Rows.Count, "E").End(xlUp).Row

For Trial = 1 To NumTrials
Cells(Trial, 1) = Cells(1 + Int(Rnd() * LastRow), 5)
Next Trial
End Sub``````

#### diddi

##### Well-known Member
and this is from the wish list

and the code:
VBA Code:
``````Sub RollTwoDie()
Dim Trial As Long, NumTrials As Long, LastRow As Long
NumTrials = Cells(2, 5) ' the number if trials is in E2 this time

For Trial = 2 To NumTrials + 1
Cells(Trial, 1) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
Cells(Trial, 2) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
Cells(Trial, 3) = Cells(Trial, 1) + Cells(Trial, 2)
Next Trial
End Sub``````

#### diddi

##### Well-known Member
and heres the super version to add on to the previous part in the cells i have used (no formulae, and leave Q2:Q12 empty once you have the graph working)

run the code and watch the chart become more binomial in distribution (live) as it calculates. cant post a vid

VBA Code:
``````Sub RollManyTrials()
Dim Trial As Long, NumTrials As Long, LastRow As Long, RunNumber as Long, Row as Long
NumTrials = Cells(2, 5) ' the number if trials is in E2 this time

For RunNumber = 1 To Cells(2, 10)
For Trial = 2 To NumTrials + 1
Cells(Trial, 1) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
Cells(Trial, 2) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
Cells(Trial, 3) = Cells(Trial, 1) + Cells(Trial, 2)
Next Trial
For Row = 2 To 12
Cells(Row, 17) = Cells(Row, 17) + Cells(Row, 8)
Next Row
Calculate
Next RunNumber
End Sub``````

#### Fluff

##### MrExcel MVP, Moderator
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Cross posted at: Creating a Number Generator - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Replies
8
Views
186
Replies
5
Views
224
Replies
5
Views
197
Replies
2
Views
63
Replies
1
Views
371

1,126,922
Messages
5,621,620
Members
415,847
Latest member
AlpinoHirsch

### 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.

### Which adblocker are you using?

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

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