[Excel VBA] How to Automatically Enter a Random Value from a Database into an Adjacent Cell

Introduction

With Excel VBA, you can easily extract a random value from a specific cell range (acting as a database) and automatically enter it next to a selected cell.

In this article, I will share a macro that randomly picks one item from the range A1:A100 and outputs it to the cell immediately to the right of the active cell.

What We Want to Achieve

1. Get one random item from a “database” range (e.g., Sheet1!A1:A100).
2. Output that item to the cell on the right of the selected cell (ActiveCell).
3. Use the ActiveCell as the starting point so the macro works wherever you click.

VBA Code

Copy and paste the following code into a standard module. Simply select a cell and run the macro to populate the adjacent cell with random data.

Sub FillAdjacentCell()

    Dim TargetCell As Range
    Dim DatabaseRange As Range
    Dim RandomIndex As Long
    Dim MaxRow As Long
    
    ' Initialize the random number generator
    Randomize
    
    ' Set the currently selected cell as the target
    Set TargetCell = ActiveCell
    
    ' Set the database range (e.g., A1:A100 on Sheet1)
    Set DatabaseRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A100")
    
    ' Calculate a random index based on the number of rows
    MaxRow = DatabaseRange.Rows.Count
    RandomIndex = Int((MaxRow * Rnd) + 1)
    
    ' Set the random value in the cell to the right (Offset 0, 1)
    TargetCell.Offset(0, 1).Value = DatabaseRange.Cells(RandomIndex, 1).Value

End Sub

Code Explanation

Set TargetCell = ActiveCell

This sets the cell you have currently selected as the starting point. This allows the macro to work on any row or column you choose.

DatabaseRange = ... Range("A1:A100")

This defines the source of your data. Change Sheet1 or the range A1:A100 to match your actual data location.

RandomIndex = Int((MaxRow * Rnd) + 1)

This formula generates a random integer between 1 and the total number of rows (100). Note: I added the Randomize statement at the beginning of the code. This ensures that Excel generates a new random sequence every time you restart the application.

TargetCell.Offset(0, 1)

This refers to the cell one column to the right of the selected cell.

• To input to the left: Change to Offset(0, -1).
• To input below: Change to Offset(1, 0).

Applications and Notes

Applications

• Random Assignment: Randomly assigning names or tasks to a list.
• Testing: Creating dummy data for testing purposes.
• Lottery: Using it as a simple lucky draw tool.

Important Notes

1. Empty Cells: If your database range contains empty cells, the macro might pick them and output a blank value. Ensure your data range is continuous.
2. Randomness: Without the Randomize command, the Rnd function might repeat the same sequence of numbers each time you open Excel. Always include it for better randomness.

Summary

By using VBA, you can easily build a tool that extracts random data and pastes it relative to your active position.

• It is flexible because it uses ActiveCell.
• It is adaptable to various scenarios by simply changing the DatabaseRange.
• It ensures randomness using Rnd and Randomize.

Please use this for daily tasks like simple lotteries or automatic data completion.