Thanks Bernie,
I have amended my code accordingly. Than you so much for ALL your
help, time and patience.
Have a great weekend.
All the Best.
Paul

On Jul 13, 1:37 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Paul,

You left out the column of labels:

For i = 0 To UBound(GapsTotal)
With Sheets("Gaps Data").Cells(i + 3, 3)
.NumberFormat = "0,000,000"
.Value = GapsTotal(i)
End With
Next i

should be

For i = 0 To UBound(GapsTotal)
With Sheets("Gaps Data").Cells(i + 3, 2)
.Value = "Gaps of " & Format(i, "00")
.Offset(0, 1).NumberFormat = "#,###,###"
.Offset(0, 1).Value = GapsTotal(i)
End With
Next i

I changed the format string to #,###,### so that there wouldn't be leading values.

Other than that, it works fine.

HTH,
Bernie
MS Excel MVP

"Paul Black" wrote in message

oups.com...
Brilliant Bernie, thanks VERY much for all your time, effort and
patience with regard to my request, it is appreciated.
I have added all the revised code you have provided into the Sub which
is posted below.
Would you kindly have a quick look through it to make sure everything
is OK.

Sub GapsVariable2BRevised()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim mySize As Integer
Dim GapsTotal() As Long

mySize = 49 '< Change the Size here
ReDim GapsTotal(0 To mySize - 6)

Application.ScreenUpdating = False

For i = 0 To UBound(GapsTotal)
GapsTotal(i) = 0
Next i

For A = 1 To mySize - 5
For B = A + 1 To mySize - 4
For C = B + 1 To mySize - 3
For D = C + 1 To mySize - 2
For E = D + 1 To mySize - 1
For F = E + 1 To mySize
GapsTotal(B - A - 1) = GapsTotal(B - A - 1) +
1
GapsTotal(C - B - 1) = GapsTotal(C - B - 1) +
1
GapsTotal(D - C - 1) = GapsTotal(D - C - 1) +
1
GapsTotal(E - D - 1) = GapsTotal(E - D - 1) +
1
GapsTotal(F - E - 1) = GapsTotal(F - E - 1) +
1
Next F
Next E
Next D
Next C
Next B
Next A

With Sheets("Gaps Data").Range("B2")
.Value = "Gaps"
.Offset(0,1).Value = "Total"
End With

For i = 0 To UBound(GapsTotal)
With Sheets("Gaps Data").Cells(i + 3, 3)
.NumberFormat = "0,000,000"
.Value = GapsTotal(i)
End With
Next i

i = UBound(GapsTotal) + 4

With Sheets("Gaps Data").Cells(i, 2)
.Value = "Grand Total"
.Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")"
End With

Application.ScreenUpdating = True
End Sub

Have a Great Weekend.
All the Best.
Paul

On Jul 13, 12:40 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:



Paul,

Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i),
"0,000,000")

would be better

With Sheets("Gaps Data").Cells(i + 3, 3)
.NumberFormat = "0,000,000"
.Value = GapsTotal(i)
End With

To rewrite:

Sheets("Gaps Data").Range("B2").Value = "Gaps"
Sheets("Gaps Data").Range("C2").Value = "Total"

You would use

With Sheets("Gaps Data").Range("B2")
.Value = "Gaps"
.Offset(0,1).Value = "Total"
End With

And to re-write

Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total"
Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)"

You could use

i = UBound(GapsTotal) + 4

With Sheets("Gaps Data").Cells(i, 2)
.Value = "Grand Total"
.Offset(0,1).Formula = "=SUM(C3:C" & UBound(GapsTotal) + 3 & ")"
End With

Of course, this is done with the latest version's code.....

HTH,
Bernie
MS Excel MVP

"Paul Black" wrote in message

ups.com...
Thanks very much Bernie for the revised code.

Sub Gaps4B() is absolutely fine. I will just add the Formula you
suggested in a Cell somewhere.

You are quite right, I confused myself. I meant to say about the
thousands seperator for the Total combinations in the Sub Gaps2B().
Will ...

Sheets("Gaps Data").Cells(i + 3, 3).Value = Format(GapsTotal(i),
"0,000,000")

... do the trick please.

One final point, is there any way that ...

Sheets("Gaps Data").Range("B2").Value = "Gaps"
Sheets("Gaps Data").Range("C2").Value = "Total"

... and ...

Sheets("Gaps Data").Cells(47, 2).Value = "Grand Total"
Sheets("Gaps Data").Cells(47, 3).Formula = "=SUM(C3:C46)"

... can be re-written by using With & End With for example.

Thanks in Advance.
All the Best.
Paul

On Jul 12, 10:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Paul,

For the first part, try the version below.

For the second part, I think you are confused as to how many times each of
the GAP strings actually appear. The maximum value is 44, which clearly
doesn't need to be comma separated.

The grand total ("Grand Total which should equal 39,983,816 ") is actually
13,983,816 - and note that there are 1,712,304 different combinations, for
a
count of about 8 1/6 per gap string on average.

In my last message I wrote: "It's finished - 1,712,304 unique combinations
of gaps listed, out to column BB (two columns per
record set)"

You can calculate that by simply using

=SUM(A:BB)

in a cell in column BC.

HTH,
Bernie

Private Sub GapsVariable()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim i As Integer
Dim mySize As Integer
Dim GapsTotal() As Long

mySize = 49 ' Change the size here
ReDim GapsTotal(0 To mySize - 6)

Application.ScreenUpdating = False

For i = 0 To UBound(GapsTotal)
GapsTotal(i) = 0
Next i

For A = 1 To mySize - 5
For B = A + 1 To mySize - 4
For C = B + 1 To mySize - 3
For D = C + 1 To mySize - 2
For E = D + 1 To mySize - 1
For F = E + 1 To mySize

GapsTotal(B - A - 1) = GapsTotal(B - A - 1) + 1
GapsTotal(C - B - 1) = GapsTotal(C - B - 1) + 1
GapsTotal(D - C - 1) = GapsTotal(D - C - 1) + 1
GapsTotal(E - D - 1) = GapsTotal(E - D - 1) + 1
GapsTotal(F - E - 1) = GapsTotal(F - E - 1) + 1

Next F
Next E
Next D
Next C
Next B
Next A

Sheets("Gaps Data").Range("B2").Value = "Gaps"
Sheets("Gaps Data").Range("C2").Value = "Total"

For i = 0 To UBound(GapsTotal)
Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i,
"00")
Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i)
Next i

i = UBound(GapsTotal) + 4

Sheets("Gaps Data").Cells(i, 2).Value = "Grand Total"
Sheets("Gaps Data").Cells(i, 3).Formula = "=SUM(C3:C" & UBound(GapsTotal)
+
3 & ")"

Application.ScreenUpdating = True
End Sub

"Paul Black" wrote in message

oups.com...
Brilliant Bernie,

I ran both the Subs and got the results I was after, thank you so
much.
Is there a way to adapt the Sub Gaps2B() code to make the Grand Total
float so to speak according to the categories and the Total
combinations produced. If I was to change the maximum number of balls
from 49 to say 36 for arguments sake, the Grand Total will still
appear in Cell "C47" as it does now, leaving a gap of several blank
cells above before the last Gaps Total entry. I know I can change
the ...

Dim GapsTotal(0 To 43) As Long

... and ...

For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49

... and ...

For i = 0 To 43
Sheets("Gaps Data").Cells(i + 3, 2).Value = "Gaps of " & Format(i,
"00")
Sheets("Gaps Data").Cells(i + 3, 3).Value = GapsTotal(i)
Next i

... figure of 43 to whatever. Is there a way so I don't have to
specify the number 0 to 43 in the Array but it works it out for you?.
Could possibly the LBound and UBound be used so the values do not have
to be hard coded?.

The Sub Gaps4B() works perfectly except for the fact that there are no
thousand seperators for the Total combinations for each category and
there is no Grand Total which should equal 39,983,816 combinations.

Would ...

Cells(myRow, myCol + 1).Value = Format(mySize - 5 - A - B - C - D - E,
"0,000,000")

... sort out the thousands seperator?.

Thanks in Advance.
All the Best.
Paul

On Jul 12, 4:25 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Paul,

It's finished - 1,712,304 unique combinations of gaps listed, out to
column BB (two columns per
record set), with a resulting file size of 77 meg.

HTH,
Bernie
MS Excel MVP

"Paul Black" wrote in message

roups.com...
Thanks Bernie,

I do not understand what you mean when you say ...

The new version, Gaps4B (below Gaps2B) will produce the separate
string / count columns, but it
will still take up most of a worksheet - a single column won't hold
the results.

... Does it mean that I need to add a Column OffSet or something?. Do
you mean that there is too much data for the category list produced in
Column "B" and for the Total combinations associated produced in
Column "C". This makes the data more than 65,536 rows, is that
correct. If so how can the code be adapted to cater for this?.

Thanks again in Advance.
All the Best.
Paul

On Jul 12, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:

Paul,

The new version, Gaps2B (below), will produce the grand total formula
at
the bottom.

The new version, Gaps4B (below Gaps2B) will produce the

...

read more »- Hide quoted text -

- Show quoted text -