On Sep 11, 11:47*am, Paul Black wrote:
On Sep 10, 2:24*pm, "joeu2004" wrote:





I wrote:
Try the macro below.

As you requested, that macro might generate an irregular combination -- i.e.
fewer than normal -- if nPool is not an exact multiple of nCol.

If you reach a point where you realize that you would prefer to avoid the
irregular combination (I would), use the following macro instead.

-----

Option Explicit

Sub Shuffle()

' ***** customize*****
Const rAddress As String = "b2"
Const clrAddress As String = "b:k"
' *****

Dim i As Long, j As Long, n As Long
Dim nPool As Long, nCol As Long, nRow As Long
Dim r As Range

Randomize

nPool = Application.InputBox("How Many Numbers Would You " & _
* * * * * * "Like To Randomize?", "Shuffle Size", Type:=1)
If nPool <= 0 Then End

nCol = Application.InputBox("How Many Numbers In Each " & _
* * * * * * "Combination?", "Combination Size", Type:=1)
If nCol <= 0 Then End

' determine range of output.
If nCol nPool Then nCol = nPool
nRow = Int(nPool / nCol)

' clear any previous data
Columns(clrAddress).ClearContents

' initialize pool of numbers for random drawings
ReDim num(1 To nPool) As Long
For i = 1 To nPool: num(i) = i: Next

n = nRow * nCol
If n nPool Then n = nPool

For i = 1 To n
* * ' draw next random number.
* * ' store into range, across columns first,
* * ' then down rows
* * j = 1 + Int(nPool * Rnd())
* * r(i) = num(j)

* * ' remove num(j) from pool of numbers
* * If j < nPool Then num(j) = num(nPool)
* * nPool = nPool - 1
Next

End Sub

Hi Joe,

I have run the code but it gives me an error Run-time error '91'
Object variable or With block variable not set.
I have googled for an answer but there does not seem to be a solution
for my particular problem.
The error is on line ...

r(i) = num(j)

Thanks in advance,
Paul

Hi Joe,

Please ignore the previous post, I was looking at sheet one and not
sheet two of this thread, appologies.
Your macro gives the data required, thank you.
I will try and adapt your macro so that when I use 49 numbers (or
whatever) and 6 number combinations that instead of giving me just 8
lines of 6 numbers that it gives me 8 lines of 6 numbers and 1 line of
1 number.
Thanks again.

Kind regards,
Paul