fill random cells

[hulub]

hi!
I have this problem: in one sheet a have 4 values (in the columns ABC
row1) and I want to use this 4 values to fill randomly 4 of those 2
cells of the range A1:A20 (range A1:A20 is in a new sheet not in th
same sheet with the values).
How to do this?
Thank you

 

[Lars-Åke Aspelin]

hi!
I have this problem: in one sheet a have 4 values (in the columns ABCD
row1) and I want to use this 4 values to fill randomly 4 of those 20
cells of the range A1:A20 (range A1:A20 is in a new sheet not in the
same sheet with the values).
How to do this?
Thank you!

Hi,
I assume that all four values on "one sheet" should be present once
and only once in a randomly chosen subset of the 20 cells in "new
sheet" and that the other 16 cells should be cleared.

This is one way of doing it:

Sub fill_4_values_randomly_in_20_cells()
Dim a(20) As Double
For i = 1 To 20
a(i) = Rnd()
Next i
For i = 1 To 20
Worksheets("new sheet").Cells(i, 1).Clear
For k = 1 To 4
If a(i) = Application.WorksheetFunction.Large(Array(a), k * 5)
Then
Worksheets("new sheet").Cells(i, 1) = Worksheets("one
sheet").Cells(1, k)
End If
Next j
Next i
End Sub

Hope this helps

Lars-Åke