# How to Generate Random Number in a Range with Excel VBA

Get FREE Advanced Excel Exercises with Solutions!

Not only in Excel VBA but also while working with almost all the programming languages, we often need to generate one or more random number (s) in between a range. In this article, I’ll show you how you can generate random numbers in a range with examples including both repetition and not repetition.

Generate a Random Number in a Range with Excel VBA (Quick View)

``````Sub Random_Numbers_with_Rnd()

Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)

End Sub`````` ## How to Generate Random Numbers in a Range with Excel VBA: 2 Methods

There are 2 methods available to generate a random number in a range with Excel VBA.

### 1. Generate a Random Number in a Range with the Excel RandBetween Function in VBA

First of all, we’ll generate random numbers within a range using the RandBetween function of Excel in VBA.

The syntax of the RandBetween function is very simple.

`=RANDBETWEEN (bottom, top)`

It takes two number arguments called bottom and top and generates a random number between them, including them.

Therefore, to generate a random number between 1 and 100, the VBA code will be:

⧭ VBA Code:

``````Sub Random_Numbers_with_RandBetween()

Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)

End Sub`````` ⧭ Output:

Run the code after inserting an extra line MsgBox Random_Number.

It’ll display a random number between 11 and 20. ### 2. Generate a Random Number in a Range with VBA Rnd Function

Now, we’ll generate random numbers within a range using the VBA Rnd function.

The syntax of the Rnd function is:

`=Rnd ()`

It generates a random number between 0 and 1.

To generate a random number between 1 and 100, the VBA code will be:

⧭ VBA Code:

``````Sub Random_Numbers_with_Rnd()

Bottom = 11
Top = 20
Random_Number = Bottom + Int(Rnd() * (Top - Bottom + 1))

End Sub`````` ⧭ Output:

Again run the code after inserting an extra line MsgBox Random_Number.

It’ll display a random number between 11 and 20. ## Generating Random Numbers in a Range with Repetition

Here we’ve got a data set with the names of 10 students in a school. There are 10 teams in the school consisting of numbers between 1 to 10. We need to assign each student to any of the 10 teams available randomly.

Therefore, we need to generate a series of random numbers between 1 to 10 in the range C4:C13 of the worksheet.

There may be more than one student in one group. So there may be repetitions.

We can accomplish this using both the RandBetween function and the Rnd function. But we’ll use the RandBetween function here.

If you wish, you can try the Rnd function on your own.

The VBA code with the RandBetween function will be:

⧭ VBA Code:

``````Sub Random_Numbers_with_Repetition()

Set Rng = Range("C4:C13")

Bottom = 1
Top = 10

For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Rng.Cells(i, j) = Application.WorksheetFunction.RandBetween(Bottom, Top)
Next j
Next i

End Sub`````` ⧭ Output:

Run this code. It’ll create a series of random numbers from 1 to 10 in the range C4:C13 of the worksheet with repetition. ## Generating Random Numbers in a Range without Repetition

Now, we’ll assign each student a unique team. That means we’ll generate a series of random numbers between 1 to 10 in the range C4:C13, this time without repetition.

We’ll use the Rnd function of VBA here.

⧭ VBA Code:

``````Sub Random_Numbers_without_Repetition()

Set Rng = Range("C4:C13")

Bottom = 1
Top = 10

Dim Numebrs() As Variant
ReDim Numbers(Top - Bottom)

For i = LBound(Numbers) To UBound(Numbers)
Numbers(i) = Bottom + i
Next i

For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Index = LBound(Numbers) + Int(Rnd() * (UBound(Numbers) - LBound(Numbers) + 1))
Rng.Cells(i, j) = Numbers(Index)
For k = Index To UBound(Numbers) - 1
Numbers(k) = Numbers(k + 1)
Next k
If UBound(Numbers) <> 0 Then
ReDim Preserve Numbers(UBound(Numbers) - 1)
End If
Next j
Next i

End Sub`````` ⧭ Output:

Run this code. It’ll create a series of random numbers from 1 to 10 in the range C4:C13 of the worksheet without repetition. ## Things to Remember

The Rnd function is a function of VBA. That’s why we used it directly in our codes. On the other hand, the RandBetween function is not a VBA function, it’s actually an Excel function. That’s why we had to use it along with the Application.WorksheetFunction property.

## Conclusion

So, these are the ways to generate random numbers in a range using VBA in Excel. Do you have any questions? Feel free to ask us. Don’t forget to visit our site ExcelDemy for more posts and updates.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags: Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1. Reply I WANT Generate Random Number in a Range with Excel VBA BY DEFINED Number
i.e distribute number 100 random on 20 cells whereas the total sum of these cells is 100

• Reply Hi AHMED KAMEL,
Thank you for your comment. According to your comment, I have understood that you want to generate random numbers in a range with Excel VBA by defined number and distribute 100 random numbers on 20 cells whereas the total sum of these cells is 100.

To solve this issue follow the below steps:

● Insert a new module and copy and paste the following code.

`````` Sub DistributeRandomNumbers()
Dim total As Double
Dim numCells As Integer
Dim minValue As Double
Dim maxValue As Double
Dim rng As Range
Dim cell As Range
Dim i As Integer

' Define parameters
total = 100
numCells = 20
minValue = 0
maxValue = 10

' Clear existing values in the range
Set rng = Worksheets("Sheet1").Range("A1:A" & numCells)
rng.ClearContents

' Generate and distribute random numbers
Randomize ' Initialize the random number generator
For i = 1 To numCells - 1
Set cell = rng.Cells(i, 1)
cell.Value = WorksheetFunction.RandBetween(minValue, maxValue)
total = total - cell.Value
Next i
' The last cell gets the remaining value to ensure the sum is 100
rng.Cells(numCells, 1).Value = total
End Sub ``````

● Now, run the macro by pressing F5. This code will distribute 100 random integers across 20 cells in such a way that their sum equals 100. The random number range is specified by minValue and maxValue. Here is the final output image after running the VBA macro successfully. Change the sheet name according to yours. You can download the Excel file below.

Hopefully, you will be able to solve your problem now. Please feel free to reach out to us with any other questions or you can send us your Excel files as well.

Regards
Nujat Tasnim
Exceldemy. Advanced Excel Exercises with Solutions PDF  