[VBA] How to Randomly Select Names and Place Them in Cells (No Duplicates)

2025年12月30日

Excel VBA allows you to easily automate the process of randomly extracting items from a specified name list and placing them into cells.

This article introduces the procedure and VBA code for extracting random names without duplicates and arranging them in a specific layout.

Goals

Select 6 people randomly from a name list.
Arrange the selected names in 3 rows by 2 columns.
Avoid duplicates (ensure the same name is not selected twice).

VBA Code

Paste the following code into a standard module. When executed, it will place random names starting from the cell immediately to the right of the active cell.

Sub FillNamesNextToTargetCell()

    Dim TargetCell As Range
    Dim NameList As Variant
    Dim SelectedNames() As String
    Dim i As Integer, j As Integer
    Dim TempName As String, RandomIndex As Integer

    ' Initialize the random number generator
    Randomize

    ' Set the target cell as the currently active cell
    Set TargetCell = ActiveCell

    ' List of names (0-based array)
    NameList = Array("森", "小森", "中森", "大森", "林", "小林", "中林", "大林", "小木", "中木", "大木")
    
    ' Select 6 people randomly from the list
    ReDim SelectedNames(1 To 6)
    For i = 1 To 6
        Do
            ' Generate a random index between 0 and UBound(NameList)
            RandomIndex = Int((UBound(NameList) + 1) * Rnd)
            TempName = NameList(RandomIndex)
        Loop Until Not IsInArray(TempName, SelectedNames)
        SelectedNames(i) = TempName
    Next i

    ' Place the selected names in 3 rows x 2 columns
    j = 1
    For i = 1 To 6 Step 2
        ' Column 1 (Offset 1 column to the right)
        TargetCell.Offset(j - 1, 1).Value = SelectedNames(i)
        ' Column 2 (Offset 2 columns to the right)
        TargetCell.Offset(j - 1, 2).Value = SelectedNames(i + 1)
        j = j + 1
    Next i

End Sub

' Function to check for duplicates
Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
    Dim element As Variant
    On Error GoTo ErrorHandler
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
ExitFunction:
    Exit Function
ErrorHandler:
    IsInArray = False
    Resume ExitFunction
End Function

Explanation of the Code

Main Procedure: FillNamesNextToTargetCell

Randomize: Initializes the random number generator. Without this, the same sequence of random names might be selected every time Excel is restarted.
TargetCell = ActiveCell: Sets the starting point for placement to the cell selected at the time of execution.
NameList: The list of names to choose from. You can modify the names or number of items in this array as needed.
Duplicate Prevention Loop (Do…Loop Until): The code repeatedly generates a random index and picks a name. The IsInArray function checks if the name is already in SelectedNames. If it is (duplicate), the loop repeats until a unique name is found.
Offset(j – 1, 1): Places the names in a grid layout (2 columns per row) to the right of the active cell.

Helper Function: IsInArray

This function checks if the randomly selected TempName already exists within the SelectedNames array. It returns True if a duplicate is found, allowing the main loop to reject it and try again.

Important Notes

Infinite Loop Risk: If the number of people to select (e.g., 6) is larger than the total number of names in NameList, the code will enter an infinite loop because it can never find enough unique names. Ensure the source list is always larger than or equal to the selection count.
Random Number Generation: The calculation Int((UBound(NameList) + 1) * Rnd) is used to correctly cover the range from index 0 to the last index of the array.