Random number generation help needed urgently

N

nitar

New Member
Joined
Sep 21, 2011
Messages
7
Hi,

Im trying to generate random scores for about 200 students for 10 courses. Each student is only allowed to do 5 courses.
Please any help with how to do this using excel vba?
Thanks in advance!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Scores from 1 to 2? One score per student?

You might wish to provide a teensy-weensy tad more detail.
 
Upvote 0
GTO said:
Scores from 1 to 2? One score per student?

You might wish to provide a teensy-weensy tad more detail.
Click to expand...

Thanks,

I want scores from 45 to 90. Each student is must have scores for 5 out of the 10 courses available.

Is this better?
 
Upvote 0
help anyone? I'm new here, so i don't know how to paste an excel sheet showing what I want it to look like when its done :(
 
Upvote 0
Hi there,

By simple example, you could pick whatever cells you want the scores to be in and use something like:
Rich (BB code): 
Sub exa()
Dim Cell As Range
    
    If TypeName(ActiveSheet) = "Worksheet" Then
        For Each Cell In Selection
            Cell.Value = Rnd_RetLong(45, 90)
        Next
    End If
End Sub
    
Function Rnd_RetLong(LowestNum As Long, HighestNum As Long) As Long
    Randomize
    Rnd_RetLong = Int((HighestNum - LowestNum + 1) * Rnd + LowestNum)
End Function

I don't know what your sheet is layed out like of course, but for instance to fill in random scores for random courses:

Excel Workbook
ABCDEFGHIJK
1Course Number12345678910
2Name
3Biff6584808662
4Tom6772705161
5Mary6688475182
6Sally8177828854
Sheet1


You could use (with the above listed function):
Rich (BB code): 
Sub exa2()
Dim rng     As Range
Dim rRow    As Range
Dim lRetVal As Long
Dim i       As Long
Dim aryTmp  As Variant
    
Dim DIC     As Object '<--- Dictionary
    
    Set DIC = CreateObject("Scripting.Dictionary")
    
    Set rng = Range("B3:K6")
    rng.ClearContents
    
    For Each rRow In rng.Rows
        DIC.RemoveAll
        Do While DIC.Count < 5
            lRetVal = Rnd_RetLong(1, 10)
            If Not DIC.Exists(lRetVal) Then DIC.Item(lRetVal) = Empty
        Loop
        aryTmp = DIC.Keys
        
        For i = LBound(aryTmp) To UBound(aryTmp)
            rRow.Cells(aryTmp(i)).Value = Rnd_RetLong(45, 90)
        Next
    Next
    DIC.RemoveAll
End Sub


To post sample data:
Excel Jeanie
Or
Richard Schollar's HTML Maker

Hope that helps,

Mark
 
Upvote 0
GTO said:
Hi there,

By simple example, you could pick whatever cells you want the scores to be in and use something like:
Rich (BB code): 
Sub exa()
Dim Cell As Range
    
    If TypeName(ActiveSheet) = "Worksheet" Then
        For Each Cell In Selection
            Cell.Value = Rnd_RetLong(45, 90)
        Next
    End If
End Sub
    
Function Rnd_RetLong(LowestNum As Long, HighestNum As Long) As Long
    Randomize
    Rnd_RetLong = Int((HighestNum - LowestNum + 1) * Rnd + LowestNum)
End Function

I don't know what your sheet is layed out like of course, but for instance to fill in random scores for random courses:

Excel Workbook
ABCDEFGHIJK
1Course Number12345678910
2Name**********
3Biff658480*86*62***
4Tom*67*72*70*5161*
5Mary66*884751****82
6Sally*81**778288**54
Sheet1


You could use (with the above listed function):
Rich (BB code): 
Sub exa2()
Dim rng     As Range
Dim rRow    As Range
Dim lRetVal As Long
Dim i       As Long
Dim aryTmp  As Variant
    
Dim DIC     As Object '<--- Dictionary
    
    Set DIC = CreateObject("Scripting.Dictionary")
    
    Set rng = Range("B3:K6")
    rng.ClearContents
    
    For Each rRow In rng.Rows
        DIC.RemoveAll
        Do While DIC.Count < 5
            lRetVal = Rnd_RetLong(1, 10)
            If Not DIC.Exists(lRetVal) Then DIC.Item(lRetVal) = Empty
        Loop
        aryTmp = DIC.Keys
        
        For i = LBound(aryTmp) To UBound(aryTmp)
            rRow.Cells(aryTmp(i)).Value = Rnd_RetLong(45, 90)
        Next
    Next
    DIC.RemoveAll
End Sub


To post sample data:
Excel Jeanie
Or
Richard Schollar's HTML Maker

Hope that helps,

Mark
Click to expand...

Many thanks Mark, this works perfectly :biggrin: Thanks for the links as well.
 
Upvote 0
Glad to help and thank you for the feedback :-)
 
Upvote 0
GTO said:
Hi there,

By simple example, you could pick whatever cells you want the scores to be in and use something like:
Rich (BB code): 
Sub exa()
Dim Cell As Range
 
    If TypeName(ActiveSheet) = "Worksheet" Then
        For Each Cell In Selection
            Cell.Value = Rnd_RetLong(45, 90)
        Next
    End If
End Sub
 
Function Rnd_RetLong(LowestNum As Long, HighestNum As Long) As Long
    Randomize
    Rnd_RetLong = Int((HighestNum - LowestNum + 1) * Rnd + LowestNum)
End Function

I don't know what your sheet is layed out like of course, but for instance to fill in random scores for random courses:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 110px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 2px solid; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">Course Number</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">1</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">3</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">4</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">5</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">6</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">7</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">8</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">9</TD><TD style="BORDER-BOTTOM: #000000 3px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; BORDER-TOP: #000000 2px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 2px solid">10</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 2px solid; BACKGROUND-COLOR: #ffcc00; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">Name</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BACKGROUND-COLOR: #ffcc00; BORDER-RIGHT: #000000 2px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 2px solid; BACKGROUND-COLOR: #ffcc00; BORDER-RIGHT: #000000 1px solid">Biff</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">65</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">84</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">80</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">86</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">62</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 2px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 2px solid; BACKGROUND-COLOR: #ffcc00; BORDER-RIGHT: #000000 1px solid">Tom</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">67</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">72</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">70</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">51</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">61</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 2px solid"> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 2px solid; BACKGROUND-COLOR: #ffcc00; BORDER-RIGHT: #000000 1px solid">Mary</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">66</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">88</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">47</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">51</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 2px solid">82</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 2px solid; BACKGROUND-COLOR: #ffcc00; BORDER-RIGHT: #000000 1px solid">Sally</TD><TD style="BORDER-BOTTOM: #000000 2px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">81</TD><TD style="BORDER-BOTTOM: #000000 2px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 2px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">77</TD><TD style="BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">82</TD><TD style="BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 1px solid">88</TD><TD style="BORDER-BOTTOM: #000000 2px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 2px solid; BORDER-RIGHT: #000000 1px solid"> </TD><TD style="BORDER-BOTTOM: #000000 2px solid; TEXT-ALIGN: right; BORDER-RIGHT: #000000 2px solid">54</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

You could use (with the above listed function):
Rich (BB code): 
Sub exa2()
Dim rng     As Range
Dim rRow    As Range
Dim lRetVal As Long
Dim i       As Long
Dim aryTmp  As Variant
 
Dim DIC     As Object '<--- Dictionary
 
    Set DIC = CreateObject("Scripting.Dictionary")
 
    Set rng = Range("B3:K6")
    rng.ClearContents
 
    For Each rRow In rng.Rows
        DIC.RemoveAll
        Do While DIC.Count < 5
            lRetVal = Rnd_RetLong(1, 10)
            If Not DIC.Exists(lRetVal) Then DIC.Item(lRetVal) = Empty
        Loop
        aryTmp = DIC.Keys
 
        For i = LBound(aryTmp) To UBound(aryTmp)
            rRow.Cells(aryTmp(i)).Value = Rnd_RetLong(45, 90)
        Next
    Next
    DIC.RemoveAll
End Sub


To post sample data:
Excel Jeanie
Or
Richard Schollar's HTML Maker

Hope that helps,

Mark
Click to expand...


Hi Mark,

Awesome code with Scripting.Dictionary which I need to learn.
You forgot put ur name in your example.

Biz
 
Upvote 0
Thank you Biz :-)

I'm not sure what you meant by I forgot to put my name in?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Random Number Generator That Can Exclude Numbers
Replies
2
Views
290
FormulasOnly
F
N
  • Question Question
Selecting multiple random names from a list, with additional criteria
2
Replies
10
Views
403
Peter_SSs
Peter_SSs
M
  • Solved
seed random number in VBA
Replies
4
Views
407
MetLife
M
Dan Wilson
  • Solved
Random Function help
Replies
4
Views
94
Dan Wilson
Dan Wilson
J
  • Question Question
how to extract randomly a number based on a matrix
Replies
4
Views
359
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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
Back
Top