Calculating Gaps Between Numbers

P

Paul Black

Bernie, thanks ever so much for all the time and effort you have given
to this, it is appreciated.

Just a couple of things though please.
I am going over to see my niece this evening so I will be able to run
the two Subs you provided below ( the second one has been updated
according to your last response ).

Firstly, for Private Sub Gaps2(), will it possible to have the Grand
Total for ALL the combinations produced in column "C" please . This
figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly
ran the Sub for me and will go after the last entry in Column "C".

Secondly, will it be possible for Private Sub Gaps4() to have ...

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

.... and the categories list starting in Cell "B3" and the Total
combinations associated with each category starting in Cell "C3"
like ...

Column "B" Column "C"
00 00 00 00 01 xxx,xxx,xxx
00 00 08 03 02 xxx,xxx,xxx
09 08 02 02 03 xxx,xxx,xxx

.... for example.
Finally, will it also be possible to have the Grand Total for ALL the
combinations produced in column "C" please, this will go after the
last entry in Column "C".

Private Sub Gaps2()

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 GapsTotal(0 To 43) As Long

Application.ScreenUpdating = False

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

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

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 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

Application.ScreenUpdating = True
End Sub

---------------------------------------------------------------------

Private Sub Gaps4()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim i As Integer
Dim mySize As Integer
Dim strGap As String
Dim myCol As Integer
Dim myRow As Long
Dim myCell As Range

Application.ScreenUpdating = False
'
myCol = 1
myRow = 1
mySize = 49 'start with a lower number here to try it....

Cells.ClearContents

For A = 0 To mySize - 5
For B = 0 To mySize - 5 - A
For C = 0 To mySize - 5 - B - A
For D = 0 To mySize - 5 - C - B - A
For E = 0 To mySize - 5 - D - C - B - A

If mySize - 5 - A - B - C - D - E > 0 Then
strGap = Format(A, "00") & " " & _
Format(B, "00") & " " & _
Format(C, "00") & " " & _
Format(D, "00") & " " & _
Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D -
E,
"0")
Cells(myRow, myCol).Value = strGap
myRow = myRow + 1
If myRow = Rows.Count + 1 Then
myRow = 1
myCol = myCol + 1
End If
End If

Next E
Next D
Next C
Next B
Next A

Application.ScreenUpdating = True
End Sub

I will then be able to run them later as I said.

Thanks in Advance.
All the Best.
Paul
 
B

Bernie Deitrick

Paul,

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

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

HTH,
Bernie
MS Excel MVP

Private Sub Gaps2B()

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 GapsTotal(0 To 43) As Long

Application.ScreenUpdating = False

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

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

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 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

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

Application.ScreenUpdating = True
End Sub


Private Sub Gaps4B()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim i As Integer
Dim mySize As Integer
Dim strGap As String
Dim myCol As Integer
Dim myRow As Long
Dim myCell As Range

Application.ScreenUpdating = False
'
myCol = 1
myRow = 3
mySize = 49 'start with a lower number here to try it....

Cells.ClearContents

Cells(2, 1).Value = "Gap"
Cells(2, 2).Value = "Total"

For A = 0 To mySize - 5
' If A <> 0 Then MsgBox "Finished " & A & " out of " & mySize - 5
For B = 0 To mySize - 5 - A
For C = 0 To mySize - 5 - B - A
For D = 0 To mySize - 5 - C - B - A
For E = 0 To mySize - 5 - D - C - B - A

If mySize - 5 - A - B - C - D - E > 0 Then
strGap = Format(A, "00") & " " & _
Format(B, "00") & " " & _
Format(C, "00") & " " & _
Format(D, "00") & " " & _
Format(E, "00")
Cells(myRow, myCol).Value = strGap
Cells(myRow, myCol + 1).Value = mySize - 5 - A - B - C - D - E
myRow = myRow + 1
If myRow = Rows.Count + 1 Then
myRow = 3
myCol = myCol + 2
Cells(2, myCol).Value = "Gap ID"
Cells(2, myCol + 1).Value = "Count"

End If
End If

Next E
Next D
Next C
Next B
Next A

Application.ScreenUpdating = True
End Sub




Bernie, thanks ever so much for all the time and effort you have given
to this, it is appreciated.

Just a couple of things though please.
I am going over to see my niece this evening so I will be able to run
the two Subs you provided below ( the second one has been updated
according to your last response ).

Firstly, for Private Sub Gaps2(), will it possible to have the Grand
Total for ALL the combinations produced in column "C" please . This
figure will be 69,919,080 as produced by Mr. Tom Ogilvy when he kindly
ran the Sub for me and will go after the last entry in Column "C".

Secondly, will it be possible for Private Sub Gaps4() to have ...

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

.... and the categories list starting in Cell "B3" and the Total
combinations associated with each category starting in Cell "C3"
like ...

Column "B" Column "C"
00 00 00 00 01 xxx,xxx,xxx
00 00 08 03 02 xxx,xxx,xxx
09 08 02 02 03 xxx,xxx,xxx

.... for example.
Finally, will it also be possible to have the Grand Total for ALL the
combinations produced in column "C" please, this will go after the
last entry in Column "C".

Private Sub Gaps2()

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 GapsTotal(0 To 43) As Long

Application.ScreenUpdating = False

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

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

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 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

Application.ScreenUpdating = True
End Sub

---------------------------------------------------------------------

Private Sub Gaps4()

Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim i As Integer
Dim mySize As Integer
Dim strGap As String
Dim myCol As Integer
Dim myRow As Long
Dim myCell As Range

Application.ScreenUpdating = False
'
myCol = 1
myRow = 1
mySize = 49 'start with a lower number here to try it....

Cells.ClearContents

For A = 0 To mySize - 5
For B = 0 To mySize - 5 - A
For C = 0 To mySize - 5 - B - A
For D = 0 To mySize - 5 - C - B - A
For E = 0 To mySize - 5 - D - C - B - A

If mySize - 5 - A - B - C - D - E > 0 Then
strGap = Format(A, "00") & " " & _
Format(B, "00") & " " & _
Format(C, "00") & " " & _
Format(D, "00") & " " & _
Format(E, "00") & " Count = " & Format(mySize - 5 - A - B - C - D -
E,
"0")
Cells(myRow, myCol).Value = strGap
myRow = myRow + 1
If myRow = Rows.Count + 1 Then
myRow = 1
myCol = myCol + 1
End If
End If

Next E
Next D
Next C
Next B
Next A

Application.ScreenUpdating = True
End Sub

I will then be able to run them later as I said.

Thanks in Advance.
All the Best.
Paul
 
P

Paul Black

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
 
B

Bernie Deitrick

Paul,

The code is written to account for that already... I have been running the code on another machine
for the last hour or so, and it is up to column AX - and should soon be done. This part of the code

myRow = myRow + 1
If myRow = Rows.Count + 1 Then
myRow = 3
myCol = myCol + 2

accounts for the needed change in column (to move the data write over two columns) and row (to move
the data writing back to the top of the column)

Just run it... but give it lots of time...

HTH,
Bernie
MS Excel MVP


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
 
B

Bernie Deitrick

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


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
 
P

Paul Black

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
 
B

Bernie Deitrick

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


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
 
P

Paul Black

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
 
B

Bernie Deitrick

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





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
 
P

Paul Black

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
 
B

Bernie Deitrick

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


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
 
P

Paul Black

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify working code to expand the output. 5
Problems with AutoFilter macro 2
Formula help, please. 3
Getting unexpected gaps in a spreadsheet 1
Count Specific Total Numbers from a Set 22
Excel macro programming question 9
Runtime error 13 : type mismatch 2
Loop thru xx No. of sheets until the four strValue combo's are found 6

Top