不重复随机数的随机数

Question

我的最终结果是按随机顺序输出A列到B列中的名称。

我一直在研究，但似乎找不到我需要的东西。

到目前为止，我可以将数字随机化，但它仍然给了我重复的数字+标题(A1)。

我需要它跳过A1，因为这是列的标题\标题，并从A2开始。

我假设一旦正确工作，我就会将randomNumber添加到Worksheets("Master Sheet").Cells(randomNumber, "B").Value ...something的随机名称中，比如.？

或者有更好的方法让我知道。

Sub Meow()

Dim CountedRows As Integer
Dim x As Integer
Dim i As Integer
Dim PreviousCell As Integer
Dim randomNumber As Integer

i = 1
PreviousCell = 0

CountedRows = Worksheets("Master Sheet").Range("A" & Rows.Count).End(xlUp).Row

If CountedRows < 2 Then
 ' If its only the heading then quit and display a messagebox
   No_People_Error = MsgBox("No People entered or found, in column 'A' of Sheetname 'Master Sheet'", vbInformation, "Pavle Says No!")
   Exit Sub
End If


Do Until i = CountedRows
 randomNumber = Int((Rnd * (CountedRows - 1)) + 1) + 1

 If Not PreviousCell = randomNumber Then
    Debug.Print randomNumber
    i = i + 1
 End If

 PreviousCell = randomNumber
Loop

Debug.Print "EOF"

End Sub

Answer 1

我以前使用过两个集合来管理类似的东西。

用原始数据填充一个集合，并保留另一个集合为空。然后继续在第一个集合中随机选择一个索引，将该索引的值添加到第二个集合中，并从原始集合中删除该值。将其设置为循环，直到第一个集合为空，第二个集合将满是从起始列表中随机排序的唯一值集。

*编辑:我又想过了，你不需要第二个收藏。您可以从第一个集合中弹出一个随机值，并将其直接写入工作表，每次递增行：

Sub Meow()

Dim lst As New Collection
Dim rndLst As New Collection
Dim startRow As Integer
Dim endRow As Integer
Dim No_People_Error As Integer

startRow = 2
endRow = Worksheets("Master Sheet").Cells(startRow, 1).End(xlDown).Row

If Cells(startRow, 1).Value = "" Then
 ' If its only the heading then quit and display a messagebox
   No_People_Error = MsgBox("No People entered or found, in column 'A' of Sheetname 'Master Sheet'", vbInformation, "Pavle Says No!")
   Exit Sub
End If

' Fill a collection with the original list
Dim i As Integer
For i = startRow To endRow
    lst.Add Cells(i, 1).Value
Next i


' Create a randomized list collection
' Use i as a row counter
Dim rowCounter As Integer
rowCounter = startRow

Dim index As Integer

Do While lst.Count > 0

    'Find a random index in the original collection
    index = Int((lst.Count - 1 + 1) * Rnd + 1)
    'Place the value in the worksheet
    Cells(rowCounter, 2).Value = lst(index)
    'Remove the value from the list
    lst.Remove (index)
    'Increment row counter
    rowCounter = rowCounter + 1

Loop

End Sub

我希望在给你的分Meow()命名后有一个很好的故事:P

Answer 2

这是一个快速的黑客..。

  Sub Meow()
    'On Error GoTo err_error
    Dim CountedRows As Integer
    Dim x As Integer
    Dim i As Integer
    Dim PreviousCell As Integer
    Dim randomNumber As Integer
    Dim nums() As Integer
    PreviousCell = 0

    CountedRows = Worksheets("Master Sheet").Range("A" & Rows.Count).End(xlUp).Row
    ReDim nums(CountedRows - 1)
    If CountedRows < 2 Then
     ' If its only the heading then quit and display a messagebox
       No_People_Error = MsgBox("No People entered or found, in column 'A' of Sheetname 'Master Sheet'", vbInformation, "Pavle Says No!")
       Exit Sub
    End If

    For i = 1 To CountedRows
    rand:
        randomNumber = randomNumbers(1, CountedRows, nums)
        nums(i - 1) = randomNumber
        Worksheets("Master Sheet").Range("B" & randomNumber) = Range("A" & i)
    Next i


    Exit Sub
    err_error:
    Debug.Print Err.Description
    End Sub

    Public Function randomNumbers(lb As Integer, ub As Integer, used As Variant) As Integer
    Dim r As Integer
    r = Int((ub - lb + 1) * Rnd + 1)
    For Each j In used
        If j = r Then
            r = randomNumbers(lb, ub, used)
        Else
            randomNumbers = r
        End If
    Next

    End Function