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

[Excript]

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 <!-- / message --> <!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --> <!-- END TEMPLATE: ad_showthread_firstpost_sig --> <!-- controls -->

 

[Sandeep Warrier]

Try this...

Sub test()
    Dim rng As Range
    Dim selectionrange As Range
    Dim i As Integer
    
    Set selectionrange = Application.InputBox("Please Select Your Range", Type:=8)
    
    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]

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

 

[Sandeep Warrier]

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.