fill random cells
 

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? :confused:
Thank you

--
hulu
-----------------------------------------------------------------------
hulub's Profile: http://www.excelforum.com/member.php...fo&userid=1449
View this thread: http://www.excelforum.com/showthread.php?threadid=26141

fill random cells
 

On Sun, 19 Sep 2004 12:52:51 -0500, hulub
wrote:


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? :confused:
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