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 -