Generate random values from a range
To generate random values from a range you can use a combination of Excel INDEX, RANDBETWEEN, ROWS and COLUMNS functions, using Excel and VBA
Example: Generate random values from a range
|
=INDEX(B5:C9,RANDBETWEEN(1,ROWS(B5:C9)),RANDBETWEEN(1,COLUMNS(B5:C9)))
|
This formula uses a combination of Excel INDEX, RANDBETWEEN, ROWS and COLUMNS functions to generate a random value from a range.
|
Sub Generate_random_values_from_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'generate a random values from a range
ws.Range("E5") = WorksheetFunction.Index(Range("B5:C9"), WorksheetFunction.RandBetween(1, ws.Range("B5:C9").Rows.Count), WorksheetFunction.RandBetween(1, ws.Range("B5:C9").Columns.Count))
ws.Range("E5") = WorksheetFunction.Index(Range("B5:C9"), WorksheetFunction.RandBetween(1, ws.Range("B5:C9").Rows.Count), WorksheetFunction.RandBetween(1, ws.Range("B5:C9").Columns.Count))
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Range: If using the exact same VBA code, with the same parameters, you need to capture the values that you want to randomly generate in range ("B5:C9").
Worksheet Name: Have a worksheet named Analysis.
Range: If using the exact same VBA code, with the same parameters, you need to capture the values that you want to randomly generate in range ("B5:C9").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range from which you want to generate a random value by changing the range ("B5:C9") in the VBA code to any range that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell references ("E5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range from which you want to generate a random value by changing the range ("B5:C9") in the VBA code to any range that doesn't conflict with the formula.
EXPLANATION
This tutorial shows and explains how to generate random values from a range, by using an Excel formula or VBA.
This tutorial shows and explains how to generate random values from a range, by using an Excel formula or VBA.
Excel Method: This tutorial provides a single Excel method that can be applied to generate random values from a range, using a combination of Excel INDEX, RANDBETWEEN, ROWS and COLUMNS functions.
VBA Methods: This tutorial provides a single VBA method that generates random values from a range.
FORMULA
INDEX(range,RANDBETWEEN(1,ROWS(range)),RANDBETWEEN(1,COLUMNS(range)))
INDEX(range,RANDBETWEEN(1,ROWS(range)),RANDBETWEEN(1,COLUMNS(range)))
ARGUMENTS
range: A range that captures the values that you want to randomly generate.
range: A range that captures the values that you want to randomly generate.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Generate random numbers | How to generate random numbers between 0 and 1 using Excel and VBA methods | |
| Generate random number between two numbers | How to generate random number between two specific numbers using Excel and VBA methods | |
| Generate random values from a column | How to generate a random value from a specific range in a single column using Excel and VBA methods | |
| Generate random values from a row | How to generate a random value from a specific range using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| INDEX Function | The Excel INDEX function returns a value that is referenced from a specified range | |
| RANDBETWEEN Function | The Excel RANDBETWEEN function returns a random number between two specified numbers | |
| COLUMNS Function | The Excel COLUMNS function returns the number of columns in a specified array | |
| ROWS Function | The Excel ROWS function returns the number of rows in a specified array |