Here’s a quick overview of the code that assigns random numbers.

```
Sub Random_Numbers_with_Rnd()
Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)
End Sub
```

**Method 1 – Using the Excel RandBetween Function in VBA**

We’ll generate random numbers within a range using the **RandBetween function** of Excel in **VBA**.

The syntax of the **RandBetween **function is:

`=RANDBETWEEN (bottom, top)`

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

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

```
Sub Random_Numbers_with_RandBetween()
Bottom = 11
Top = 20
Random_Number = Application.WorksheetFunction.RandBetween(Bottom, Top)
End Sub
```

- Run the code after inserting an extra line
**MsgBox Random_Number**.

It’ll display a random number between **11** and **20**.

**Method 2 – Using the VBA Rnd Function**

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:

```
Sub Random_Numbers_with_Rnd()
Bottom = 11
Top = 20
Random_Number = Bottom + Int(Rnd() * (Top - Bottom + 1))
End Sub
```

- 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**

Below is a dataset with the names of **10 **students in a school.

There are **10** teams in the school, each with a number between 1 and 10. We need to assign each student to one of the **10** teams available randomly.

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.

You can try the **Rnd** function on your own.

- The
**VBA**code with the**RandBetween**function will be:

```
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
```

- 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.

**Read More:** How to Generate Random Number with Excel VBA

**Generating Random Numbers in a Range without Repetition**

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:**

```
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
```

- 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 VBA function, so 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, so we had to use it along with the **Application.WorksheetFunction** property.

**Download the Practice Workbook**

**Related Articles**

- Excel VBA: Random Number Generator with No Duplicates
- Random Number Generator with Normal Distribution in Excel

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

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.

● 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.

https://www.exceldemy.com/wp-content/uploads/2023/08/Answer.xlsm

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.