[VBA] How to Randomly Select a Cell and Input a Value

Background

While developing a tool in VBA, I encountered a situation where I needed to “randomly select one cell from a list and input a value into it.”

I realized this could be applied to creating seating charts or random assignment tasks, so I wrote the code to implement it.

In this example, the code randomly selects one cell from multiple specified candidates and inputs a specific value (here, “mori”).

Specifications

• Prepare the value “mori”.
• Randomly select one cell from a pre-determined group of cells.
• Specify the target cells using an Array().

Implementation Code (VBA)

Public Sub RandamRandam()
    Dim result As String
    result = "mori"
    
    ' Create a list of cells to fill randomly
    Dim cellsToFill As Variant
    cellsToFill = Array("D8", "D9", "F8", "F9", "H5", "H6", "H8", "H9", "H13", "H14", "H15", "H16")

    ' Initialize random number generator
    Randomize

    ' Set the value to the randomly selected cell
    Dim selectedCell As String
    selectedCell = cellsToFill(Int((UBound(cellsToFill) + 1) * Rnd))
    Sheet1.Range(selectedCell).Value = result
End Sub

When you run this code, one cell is randomly chosen from the specified list, and the string “mori” is entered into it.

Code Explanation

• cellsToFill: Defines the target cells for random selection in an array.
• Randomize: Initializes the random number generator. This ensures that the Rnd function produces a different result every time the code runs.
• UBound(): Retrieves the maximum index number of the array.
• Int((UBound(...) + 1) * Rnd): Randomly calculates an index number within the range of the array.

Tips for Application

• Dynamic Data: By replacing the part result = "mori" with a variable (for example, a value read from a barcode scanner), you can input dynamic data.
• Multiple Inputs: If you loop this process, you can fill multiple random cells.
• Formatting: Combining this with Range().Interior.Color allows you to randomly color cells, which is useful for visual randomization.

Summary

• By combining Array() and Rnd, you can randomly select from a specific group of cells and input values.
• Although the mechanism is simple, it can be used for automatic seating assignments or lottery tools.
• Depending on how you apply it, this function can serve as a useful support tool for business tasks or events.

I hope this helps you with your VBA projects.