# Randomly placing 5 same alphabet in a range of 20 cells. (Random Generating)

#### Excript

##### New Member
Hi, I desperately need help with something.

I would like excel to randomly pick cells and insert an alphabet to a range of cells.

For example.... 1 row of 20 and i would like excel to randomly pick 5 out of the 20 cells and insert an alphabet into it.

someone posted this before

Cell 1 =IF(RAND() <= 0.2, "a", "")
Cell 2 Drag to cell 20 =IF(RAND() < (4 - COUNTIF(B\$1:B1, "a") )/ (21 - ROW()), "a", "")

Problem with this formula is that the excel sheet will auto generate when i click on other cells or when i start filtering.

Appreciate it if someone is able to help

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Sandeep Warrier

##### Well-known Member
Try this...

Code:
``````Sub test()
Dim rng As Range
Dim selectionrange As Range
Dim i As Integer

For Each rng In selectionrange.Cells

i = Rnd() * 10

If i <= 1 Then
rng.Value = "a"
ElseIf i > 2 And i <= 3 Then
rng.Value = "b"
ElseIf i > 5 And i <= 6 Then
rng.Value = "c"
ElseIf i > 7 And i <= 8 Then
rng.Value = "d"
ElseIf i > 9 And i <= 10 Then
rng.Value = "e"
End If

Next rng
End Sub``````

#### Excript

##### New Member
err...it generates different alphabets...can i have only one alphabet only and have 5 being placed randomly in 20 selected cells?

Last edited:

#### Sandeep Warrier

##### Well-known Member
On your excel window, press Alt+F11... in the new window that opens up, go to Insert->Module.

Once you have inserted the module, on the white area just paste the code. Save and close the new window.

In your excel sheet, click Alt+F8, select test & click on run. It will 1st ask you to select a range. Just select the required range and click on OK.

Replies
3
Views
741
Replies
0
Views
647
Replies
1
Views
826
Replies
7
Views
396
Replies
14
Views
3K

1,190,959
Messages
5,983,863
Members
439,867
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back