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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
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
GTO said:
Thank you Biz :)

I'm not sure what you meant by I forgot to put my name in?
Click to expand...
Under Name column
it has Biff,Tom, Mary, Sally where is 'Mark' ;)

Biz
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Need help with random number generator
Replies
3
Views
401
Fluff
Fluff
S
  • Question
Vba - generate random alphanumeric strings based on user input
Replies
1
Views
203
DanteAmor
DanteAmor
B
  • Solved
Random select 10% for each name and Highlight it.
2
Replies
10
Views
208
eiloken
E
R
  • Question
Need help with random number generating
Replies
2
Views
411
royland27
R
J
  • Solved
Vba code to generate a list of numbers in random order
Replies
4
Views
353
Fluff
Fluff

Forum statistics

Threads
1,215,480
Messages
6,125,049
Members
449,206
Latest member
Healthydogs

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