Random numbers

[drblackard]

I do know how to use RAND() to get random numbers and RANBETWEEN(0,9)to get it to select from 0 to 9 but what I am looking for is a random command that will select a number 0 through 9 without changing and have no duplicated numbers 0 through 9...I am making something similiar to a football square board but would like to only use one set of 0 to 9 numbers...Thanks in advance for your help...

 

[CraigDolphin]

See the attached file. You need to create a code module in your workbook and place the following code in it:

'******************************************************
   'Purpose: produce array of random ints in a range
   'Inputs:  nStart - the smallest value in the range
   '         nEnd - the largest value in the range
   'Returns: array of integers
   '
   'Revised: 14 June 2005 to avoid randomizing vArr()
   '******************************************************
    Public Function RandInt( _
        Optional ByVal nStart As Long = 1&, _
        Optional ByVal nEnd As Long = -2147483647) As Variant
        'returns array of random longs
        'J.E. McGimpsey  http://www.mcgimpsey.com/excel/randint.html
        Dim vArr As Variant
        Dim vResult As Variant
        Dim nCount As Long
        Dim nIndex As Long
        Dim nRand As Long
        Dim nRows As Long
        Dim nCols As Long
        Dim i As Long
        Dim j As Long
        Application.Volatile
        If Not TypeOf Application.Caller Is Range Then Exit Function
        With Application.Caller
            nCount = .Count
            If nEnd < nStart Then nEnd = nStart + nCount - 1
            If nCount = 1 Then
                RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
            ElseIf nCount > nEnd - nStart + 1 Then
                RandInt = CVErr(xlErrNum)
            Else
                nRows = .Rows.Count
                nCols = .Columns.Count
                nIndex = nEnd - nStart + 1
                ReDim vResult(1 To nRows, 1 To nCols)
                ReDim vArr(0 To nIndex - 1)
                For i = 0 To UBound(vArr)
                    vArr(i) = i + nStart
                Next i
                For i = 1 To nRows
                    For j = 1 To nCols
                        nRand = Int(Rnd() * nIndex)
                        nIndex = nIndex - 1
                        vResult(i, j) = vArr(nRand)
                        vArr(nRand) = vArr(nIndex)
                     Next j
                Next i
                RandInt = vResult
            End If
        End With
    End Function

Then select the array of cells you want the numbers to appear in and type in the formula =RandInt(0,9) in the formula bar, then shift-ctrl-enter to make it an array formula.

to recalculate either press F9 or make a code button to do it.

Good luck with it.