Generate random number between two numbers
To generate a random number between two specific numbers you can use the Excel RANDBETWEEN function and apply into Excel or VBA
Example: Generate random number between two numbers
|
=RANDBETWEEN(B5,C5)
|
This formula uses the Excel RANDBETWEEN function to generate a random number between the two specified numbers 10 and -10. The RANDBETWEEN function generates a random number every time a worksheet is calculated.
|
Sub Generate_random_number_between_two_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
BottomNo = ws.Range("B5")
TopNo = ws.Range("C5")
TopNo = ws.Range("C5")
'generate a random number between two specified numbers
ws.Range("B5") = WorksheetFunction.RandBetween(BottomNo, TopNo)
ws.Range("B5") = WorksheetFunction.RandBetween(BottomNo, TopNo)
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Top and Bottom Numbers: Ensure that the top and bottom numbers between which you want to generate a random number are captured in cells ("C5") and ("B5"), respectively.
Worksheet Name: Have a worksheet named Analysis.
Top and Bottom Numbers: Ensure that the top and bottom numbers between which you want to generate a random number are captured in cells ("C5") and ("B5"), respectively.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Top and Bottom numbers: Select the top and bottom numbers between which you want to generate a random number by changing the cell references ("C5") and ("B5"), respectively, in the VBA code to any number that doesn't conflict with the formula. Alternatively, change the values in cells ("C5") and ("B5").
Output Range: Select the output range by changing the cell references ("D5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Top and Bottom numbers: Select the top and bottom numbers between which you want to generate a random number by changing the cell references ("C5") and ("B5"), respectively, in the VBA code to any number that doesn't conflict with the formula. Alternatively, change the values in cells ("C5") and ("B5").
METHOD 2. Generate random number between two numbers using VBA with a For Loop
VBA
Sub Generate_random_number_between_two_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'generate multiple random numbers between specified numbers by looping through cells
For x = 5 To 6
BottomNo = ws.Range("B"&x)
TopNo = ws.Range("C"&x)
TopNo = ws.Range("C"&x)
ws.Range("B"&x) = WorksheetFunction.RandBetween(BottomNo, TopNo)
Next
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Top and Bottom Numbers: Ensure that the top and bottom numbers between which you want to generate a random number are captured in ranges ("C5:C6") and ("B5:B6"), respectively.
Worksheet Name: Have a worksheet named Analysis.
Top and Bottom Numbers: Ensure that the top and bottom numbers between which you want to generate a random number are captured in ranges ("C5:C6") and ("B5:B6"), respectively.
ADJUSTABLE PARAMETERS
Output Column Range: Select the output column range by changing the column references ("B") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Output Row Range: Select the output row range by changing the for and to x values in the VBA code, that don't conflict with the formula.
Top and Bottom Column numbers Range: Select the top and bottom columns by changing the B and C column references, that don't conflict with the formula.
Top and Bottom Row numbers Range: Select the top and bottom rows by changing the for and to x values in the VBA code, that don't conflict with the formula.
Output Column Range: Select the output column range by changing the column references ("B") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Output Row Range: Select the output row range by changing the for and to x values in the VBA code, that don't conflict with the formula.
Top and Bottom Column numbers Range: Select the top and bottom columns by changing the B and C column references, that don't conflict with the formula.
Top and Bottom Row numbers Range: Select the top and bottom rows by changing the for and to x values in the VBA code, that don't conflict with the formula.
EXPLANATION
This tutorial shows and explains how to generate random numbers between two specified numbers, by using an Excel formula or VBA.
This tutorial shows and explains how to generate random numbers between two specified numbers, by using an Excel formula or VBA.
Excel Method: This tutorial provides a single Excel method that can be applied to generate random numbers between two numbers, using the Excel RANDBETWEEN function.
VBA Methods: This tutorial provides a two VBA methods. The first method generates one random number between two numbers. The second method generates multiple random numbers between two numbers through the use of the For Loop method.
FORMULA
=RANDBETWEEN(bottom,top)
=RANDBETWEEN(bottom,top)
ADDITIONAL NOTES
bottom: A number that represents the smallest value that the function can return. This number must be an integer.
top: A number that represents the largest value that the function can return. This number must be an integer.
bottom: A number that represents the smallest value that the function can return. This number must be an integer.
top: A number that represents the largest value that the function can return. This number must be an integer.
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 |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| RANDBETWEEN Function | The Excel RANDBETWEEN function returns a random number between two specified numbers |