Hi everyone,

I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings. Here is the
code :-

Option Explicit
Option Base 1

Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Select

For i = MinDist To MaxDist
DistSum(i) = 0
Next i

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

DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1

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

With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"

' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2

For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)

' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i

' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value

' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul

Make the sime change below in the for loop

from
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
to
For i = 0 to ( MaxDist - MinDist + 1)


"Paul Black" wrote:

Hi everyone,

I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings. Here is the
code :-

Option Explicit
Option Base 1

Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Select

For i = MinDist To MaxDist
DistSum(i) = 0
Next i

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

DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1

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

With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"

' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2

For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)

' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i

' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value

' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul

Hi Joel,

I get a runtime error 9, subscript out of range.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:04 pm, Joel wrote:
Make the sime change below in the for loop

from
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
to
For i = 0 to ( MaxDist - MinDist + 1)



"Paul Black" wrote:
Hi everyone,

I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings. Here is the
code :-

Option Explicit
Option Base 1

Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Select

For i = MinDist To MaxDist
DistSum(i) = 0
Next i

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

DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1

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

With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"

' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2

For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)

' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i

' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value

' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -

I missed the statement Option Base 1 which says you are stating you arrays at
one not zero. You are getting the error because the code is referecing index
zero in the array

For i = 1 to ( MaxDist - MinDist + 1)

If you still get a one row gap then you need to make the additional change
on all the writes to cells in this for loop.

from
..Offset(i + 1, 0).Value = i
to
..Offset(i, 0).Value = i


"Paul Black" wrote:

Hi Joel,

I get a runtime error 9, subscript out of range.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:04 pm, Joel wrote:
Make the sime change below in the for loop

from
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
to
For i = 0 to ( MaxDist - MinDist + 1)



"Paul Black" wrote:
Hi everyone,

I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings. Here is the
code :-

Option Explicit
Option Base 1

Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Select

For i = MinDist To MaxDist
DistSum(i) = 0
Next i

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

DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1

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

With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"

' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2

For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)

' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i

' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value

' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -

Hi Joel,

Why does this work ...

Option Explicit
Option Base 1

Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim I As Integer, nSum(279) As Long

Sub SumAll()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("B2").Select

For i = 21 To 279
nSum(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
nSum(A + B + C + D + E + F) = nSum(A + B + C + D + E + F)
+ 1
Next F
Next E
Next D
Next C
Next B
Next A

For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I

Application.ScreenUpdating = False
Range("A1").Select
End Sub

.... and my one doesn't.
Is it the fact that I use "i + 1" or something. Is using "i + 1" the
right thing to use.
Also, when I ran your code it started at 0 and went to 259, when it
should have started at 21 to 279.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:22 pm, Paul Black wrote:
Hi Joel,

I get a runtime error 9, subscript out of range.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:04 pm, Joel wrote:



Make the sime change below in the for loop

from
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
to
For i = 0 to ( MaxDist - MinDist + 1)

"Paul Black" wrote:
Hi everyone,

I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings. Here is the
code :-

Option Explicit
Option Base 1

Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Select

For i = MinDist To MaxDist
DistSum(i) = 0
Next i

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

DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1

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

With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"

' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2

For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)

' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i

' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value

' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Erase your worksheet and run the code again. I think you are getting fooled
that the code you posted works.

this code keeps on writing to the same cell. It doesn't increment the row
counter

For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I


"Paul Black" wrote:

Hi Joel,

Why does this work ...

Option Explicit
Option Base 1

Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim I As Integer, nSum(279) As Long

Sub SumAll()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("B2").Select

For i = 21 To 279
nSum(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
nSum(A + B + C + D + E + F) = nSum(A + B + C + D + E + F)
+ 1
Next F
Next E
Next D
Next C
Next B
Next A

For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I

Application.ScreenUpdating = False
Range("A1").Select
End Sub

.... and my one doesn't.
Is it the fact that I use "i + 1" or something. Is using "i + 1" the
right thing to use.
Also, when I ran your code it started at 0 and went to 259, when it
should have started at 21 to 279.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:22 pm, Paul Black wrote:
Hi Joel,

I get a runtime error 9, subscript out of range.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:04 pm, Joel wrote:



Make the sime change below in the for loop

from
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
to
For i = 0 to ( MaxDist - MinDist + 1)

"Paul Black" wrote:
Hi everyone,

I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings. Here is the
code :-

Option Explicit
Option Base 1

Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Select

For i = MinDist To MaxDist
DistSum(i) = 0
Next i

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

DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1

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

With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"

' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2

For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)

' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i

' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value

' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Hi Joel,

I did as you suggested and the second code worked perfectly.
I really want my original code to work though please.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 2:02 pm, Joel wrote:
Erase your worksheet and run the code again. I think you are getting fooled
that the code you posted works.

this code keeps on writing to the same cell. It doesn't increment the row
counter

For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I



"Paul Black" wrote:
Hi Joel,

Why does this work ...

Option Explicit
Option Base 1

Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim I As Integer, nSum(279) As Long

Sub SumAll()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("B2").Select

For i = 21 To 279
nSum(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
nSum(A + B + C + D + E + F) = nSum(A + B + C + D + E + F)
+ 1
Next F
Next E
Next D
Next C
Next B
Next A

For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I

Application.ScreenUpdating = False
Range("A1").Select
End Sub

.... and my one doesn't.
Is it the fact that I use "i + 1" or something. Is using "i + 1" the
right thing to use.
Also, when I ran your code it started at 0 and went to 259, when it
should have started at 21 to 279.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:22 pm, Paul Black wrote:
Hi Joel,

I get a runtime error 9, subscript out of range.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 1:04 pm, Joel wrote:

Make the sime change below in the for loop

from
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
to
For i = 0 to ( MaxDist - MinDist + 1)

"Paul Black" wrote:
Hi everyone,

I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings. Here is the
code :-

Option Explicit
Option Base 1

Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B2").Select

For i = MinDist To MaxDist
DistSum(i) = 0
Next i

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

DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1

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

With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"

' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2

For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)

' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i

' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value

' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -