Random?

  • Thread starter Thread starter Steven Hook
  • Start date Start date
S

Steven Hook

Hi
I need to take numbers from a list (the list is R1:R20) and use them
randomly to fill cells C2:H65535 on 15 sheets , BUT no row can have nore
than 2 of the same number, I don't know if it'll be
possible, but it's a lot of cells to fill be hand!
Maybe a macro?

Thanks again!
Steven
 
Copy and Paste Special|Values from R1:R20 twice into a column (say
AR1:AR40) and put =RAND() in the adjacent column (say AS1:AS40). The
macro should then sort both columns (AR1:AS40) by the second column
(AS1:AS40) and Copy/Paste Special|Transpose the first 6 values (AR1:AR6)
into the next row. Repeat from the sort 65535*15 times to fill the
65535 rows on the 15 sheets.

Jerry
 
Jerry said:
Copy and Paste Special|Values from R1:R20 twice into a column (say
AR1:AR40) and put =RAND() in the adjacent column (say AS1:AS40). The
macro should then sort both columns (AR1:AS40) by the second column
(AS1:AS40) and Copy/Paste Special|Transpose the first 6 values
(AR1:AR6) into the next row. Repeat from the sort 65535*15 times to
fill the 65535 rows on the 15 sheets.
Click to expand...

Mmm, That's really smart, would never have thought of it!
One thing tho, I've never writen a macro, I've recorded a few for quick
tasks, but I think this is a bit advanced for that, how do I start writing
it, and how do I do the repeat thing?

Thanks SO much for your help!

Steven
 
I managed to make a macro to do it once, but I can't figure out how to get
it to do it repeatedly for all the rows, never mind all the sheets.
Thanks
Steven
 
You have already done the hard part. When you record the macro to sort
and paste the first row, you get something like

Range("AR1:AS40").Select
Selection.Sort Key1:=Range("AS1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("AR1:AR6").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

(various unnecessary statements have been removed). Wrap this in a
For/Next loop and you are almost done.

Dim i As Long
For i = 2 To 65535
Range("AR1:AS40").Select
Selection.Sort Key1:=Range("AS1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("AR1:AR6").Select
Selection.Copy
Range("C" & i).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Next i

Then wrap this loop in an outer loop for each of the 15 destination
workbooks in turn (you will have to select the focus back an forth from
the sort sheet to the destination sheet).

Jerry
 
Back
Top