Random number generator with history

[Firecop1348]

I need to create a macro that will not only generate a random number but also will record a list of past numbers already generated on a different sheet. The numbers would be between 0 and 20 with the number showing up in cell A-5 (Main Sheet) when the macro is run and past numbers on sheet 2 starting at cell A-7.

Thanks in advance and I did do a search for this before posting, just didnt find any that would work.

 

[Marcelo Branco]

Hi,

Maybe the macro below

Sub testRandom()
    Dim aRow As Long
    
    Sheets("Main").Range("A5") = Application.WorksheetFunction.RandBetween(0, 20)
    
    With Sheets("Sheet2")
        If .Range("A7") = "" Then
            aRow = 7
        Else
            aRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        End If
        .Range("A" & aRow) = Sheets("Main").Range("A5")
    End With
End Sub

HTH

M.

 

[Misca]

Here's a couple of alternatives:

Sub RandomNumberGenerator()

'Generates all the numbers between 0 - 20 and saves them to a history log.

Dim RndNmbr As Integer

    RndNmbr = Int((20 - 0 + 1) * Rnd + 0)   'The syntax = Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

'Sheets("Main").Range("A5").Value = RndNmbr
MsgBox RndNmbr

'Writes the value to the history log:
Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = RndNmbr

End Sub

Sub RandomNumberGenerator2()

'Generates all the numbers between 0 - 20 and saves them to a history log.
'Once all the numbers have been generated, clears the history and starts over again.

Dim RndNmbr As Integer
Dim History As Range

'Sets the range to look if the RndNmbr has already been generated before:
With Sheets(2)
    Set History = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
    
    'When all the numbers have been generated:
        If History.Rows.Count = 20 Then
            History.Clear       'Clears the history
            Set History = .Range("A2")  'Starts over again
        End If
End With

Generate:
    RndNmbr = Int((20 - 0 + 1) * Rnd + 0)   'The syntax = Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

'Generare another RndNmbr if the current rndnbr was found from the history
    If Application.WorksheetFunction.CountIf(History, RndNmbr) > 0 Then GoTo Generate

'Sheets("Main").Range("A5").Value = RndNmbr
MsgBox RndNmbr

'Writes the value to the history log:
Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = RndNmbr

End Sub

The macros display the random number in a MsgBox and start the History from A2 in the second sheet. You might want to change the ranges etc.
The first one doesn't care if the random number has already been generated or not. The second one does: If the number generated is found from the History, it generates another. When all the numbers have been used, it clears the history and starts again.

 

[Firecop1348]

Thanks to all of you. They all work great; all I have to do is pick what one works best. LOL.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
You have saved me a lot of trouble, I work full time at a Max security prison. I have 20 cubes 8 inmates in a cube (yes 160 to 2)When my partner and I do shakedowns we get accused of "profiling" or picking on people. Now all I have to do is click, get my numbers and do my job. <o></o>