Unique Random Number Generation

[MINCUS1308]

I'm making minesweeper in excel because they don't have any games on the computers at work.
As part of this, I'm attempting to generate 'x' unique random numbers and then store them in MyArray.

     
For i = 1 To NoOfMines
          Do
               MineValue = CInt(Int((Size * Size - 1) * Rnd + 1))
          Loop Until IsError(Application.Match(CInt(MineValue), MyArray, 0))
          MyArray(i, 0) = MineValue
     Next I

Typically I generate 'x-1' unique numbers with 1 number repeated. '[(x-1)+1=x]
Every now and again I get 'x' unique numbers but its pretty rare.

I cannot identify a pattern of repeats associated with the iteration of 'i'.
Any thoughts about the folly in my logic?

[Jan Karel Pieterse]

I would simply create a two-column table with all the mines in columnA and the rand function in B and then do a calc and subsequently sort on col B. Then simply take the number of items from the top.

[Furtado]

Hi Jan, can you explain how to do this exactly, please? For the less knowledgeable people like me. It looks like this is perfect for something I'm doing as well. Cheers.

[Jan Karel Pieterse]

Suppose you have employee names in column A (starting from A2) and you want to randomly draw 10 employees.
In B2, enter =RAND()
copy down to match the # of employees
Select A and B and sort on column B. the top 10 employees are your draw. Want a new set of 10? simply sort on column B again.

[Paul_Hossler]

This might be a little simpler

Option Explicit

Sub MineSweeper()

    Const Size As Long = 10
    Const NumMines As Long = 20

    Dim MineField(1 To Size, 1 To Size) As Boolean
    Dim r As Long, c As Long, n As Long
    
    n = 0
    
    Randomize
    Do While n < NumMines

        r = Int(Size * Rnd + 1)
        c = Int(Size * Rnd + 1)

        If Not MineField(r, c) Then
            MineField(r, c) = True
            n = n + 1
        End If
    Loop
End Sub

[snb]

Store the result in an Array 'sn':

Sub M_snb()
   [A1:A20] = "=rand()"
   sn = [index(rank(A1:A20,A1:A20),)]
   [A1:A20].clearcontents   
End Sub

Store the results in the worksheet

Sub M_snb()
   [A1:A20] = "=rand()"
   [A1:A20] = [index(rank(A1:A20,A1:A20),)]
End Sub