I am trying to teach the concept of randomness in a college statistics class. I was thinking of writing a macro that would loop a recalculation, and have a =RANDBETWEEN(1,100) formula in a certain cell. Then... and here's the part I cannot figure out... I want to COUNT the number of times each number comes up. That is, I want to have a list of the numbers 1-100 and a counter of some sort for each one. As the random number is generated in the RANDBETWEEN cell, it would be recorded as an occurence in the list. As the random numbers continue to be generated, the totals would continue to add up.

So, for example, the number beside "47" starts at "0". As the macro loops through the RANDBETWEEN recalculation, eventually it hits a "47." The counter for "47" then goes to "1". The looping continues and, when te RANDBETWEEN hits "47" again, the counter goes to "2".

Is this possible? If you have an easier solution, I would be open to suggestions.

Thanks.

Tom

RANDBETWEEN(1,100)
Tom

I'm not sure if that will work but see attached for my solution.

Here is the code:

Sub Auto_Open()
'Reset all counters to zero
Range("D3,H3:H102").Select
Selection.Clear
Range("H3").Select
End Sub

Sub Go_Click()
Dim Value As Integer
Dim Counter As Integer, CompareValue As Integer
Dim X As Integer, Y As Integer
Application.ScreenUpdating = False
Auto_Open
Randomize
For X = 3 To 102
Y = 3
'Generate a random number from 1 to 100
Value = Int(100 * Rnd + 1)
Range("D3").Select 'Not showing all numbers
'Range("D" & X).Select 'Show all random numbers
ActiveCell.Value = Value
Range("F3").Select
CompareValue = ActiveCell.Value

Do While CompareValue <> Value
Y = Y + 1
Range("F" & Y).Select
CompareValue = ActiveCell.Value
Loop

'Number matches, increase counter by 1
Range("H" & Y).Select
ActiveCell.Value = ActiveCell.Value + 1
Next X
Range("H3").Select
Application.ScreenUpdating = True
End Sub

Sounds like you need an array of a User Defined Data Type. This array consists of integers (generated by your RANDBETWEEN) and presumably a long integer (TALLY) that stores how frequently the integer has been chosen.


Public Type TallyCount
iMember as Integer
lTally as Long
End Type

Public Sub IncrementTally(ByRef iChosen as Integer, _
ByRef MyData() as TallyCount, _
ByRef lIndex as Long)
On Error goto Err_IncrementTally

Dim bNewEntry as Boolean
Dim i as Long

bNewEntry = True

'Check current members of array for item returned by RANDBETWEEN
For i = 1 to lIndex
If MyData(i).iMember = iChosen Then
bNewEntry = False
MyData(i).lTally = MyData(i).lTally + 1
Exit For
End if
Next i

'Add new entry to array if iChosen has not yet appeared
If bNewEntry Then
lIndex = lIndex + 1
Redim Preserve MyData(lIndex)
MyData(lIndex).iMember = iChosen
MyData(lIndex).lTally = lTally + 1
End if

Exit_IncrementTally:
Exit Sub

Err_IncrementTally:
Call ErrHandler("IncrementTally routine",Err.Number,Err.Description)
Resume Exit_IncrementTally

End Sub

Thanks, fellas. I have not had a chance to try these yet, but I certainly will. I appreciate your help with this!!!