Generate random values from a row
To generate a random value from a specific range in a single row you can use a combination of Excel INDEX, RANDBETWEEN and COLUMNS functions, using Excel and VBA
Example: Generate random values from a row
|
=INDEX(C4:G4,1,RANDBETWEEN(1,COLUMNS(C4:G4)))
|
This formula uses a combination of Excel INDEX, RANDBETWEEN and COLUMNS functions to generate a random value from a specific range in a single row. You may select a range that is across multiple rows but would be required to select a specific row number from which to generate a random value.
|
Sub Generate_random_values_from_a_row()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'generate a random values from a single column
ws.Range("I5") = WorksheetFunction.Index(Range("C4:G4"), 1, WorksheetFunction.RandBetween(1, ws.Range("C4:G4").Columns.Count))
ws.Range("I5") = WorksheetFunction.Index(Range("C4:G4"), 1, WorksheetFunction.RandBetween(1, ws.Range("C4:G4").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 ("C4:G4").
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 ("C4:G4").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("I5") 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 ("C4:G4") 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 ("I5") 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 ("C4:G4") 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 row, by using an Excel formula or VBA.
This tutorial shows and explains how to generate random values from a row, 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 row, using a combination of Excel INDEX, RANDBETWEEN and COLUMNS functions.
VBA Methods: This tutorial provides a single VBA method that generates random values from a row.
FORMULA
INDEX(range,1,RANDBETWEEN(1,COLUMNS(range)))
INDEX(range,1,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 range | How to generate random values from a 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 |