Your code works and is very flexible because you just have to change B4 to
another cell and all the locations change. My prefference is to avoid
..offset because it is harder to figure out where items are going. I would of
done the code below, but both are acceptable.

' Setup Distribution Categories
Range("B4").Value = "111111"
Range("B5").Value = "211110"
Range("B6").Value = "221100"
Range("B7").Value = "222000"
Range("B8").Value = "311100"
Range("B9").Value = "321000"
Range("B10").Value = "330000"
Range("B11").Value = "411000"
Range("B12").Value = "420000"
Range("B13").Value = "510000"
Range("B14").Value = "600000"

' Format Distribution Categories
Range("B4:B14").HorizontalAlignment = xlLeft


"Paul Black" wrote:

Hi Joel,

One FINAL question, I promise.
If I wanted to physically input values into cells for example,
would ...

With Range("B4")
' Setup Distribution Categories
.Offset(0, 0).Value = "111111"
.Offset(1, 0).Value = "211110"
.Offset(2, 0).Value = "221100"
.Offset(3, 0).Value = "222000"
.Offset(4, 0).Value = "311100"
.Offset(5, 0).Value = "321000"
.Offset(6, 0).Value = "330000"
.Offset(7, 0).Value = "411000"
.Offset(8, 0).Value = "420000"
.Offset(9, 0).Value = "510000"
.Offset(10, 0).Value = "600000"

' Format Distribution Categories
.Resize(11, 1).HorizontalAlignment = xlLeft
End With

.... be the right way to do it.

Thanks in Advance.
All the Best.
Paul

On Oct 11, 12:21 pm, Paul Black wrote:
Thanks Joel,

The program now at works perfectly, thank you, but why?.

Thanks in Advance.
All the Best.
Paul

On Oct 11, 12:06 pm, Joel wrote:



change your dim to the line below and add redim.

Dim DistSum() As Single
ReDim DistSum((6 * MaxBall) - 15)

"Paul Black" wrote:
Thanks for the re-written code Joel, it is appreciated.

One thing though, if I change the parameters ...
Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Dim DistSum(279) As Single
.... to ...
Const MinDist As Integer = 50
Const MaxDist As Integer = 250
Dim DistSum(250) As Single

.... I get an error 9, subscript out of range on line ...
DistSum(A + B + C + D + E + F) = _
DistSum(A + B + C + D + E + F) + 1

This program will be used for about 25 other calculations and ONLY the
bit in between the For .. Next loop will change. The layout will be
EXACTLY the same for all of them, so I appreciate I will not have to
change much each time thanks to you.

Thanks in Advance.
All the Best.
Paul

On Oct 11, 1:18 am, Joel wrote:
I re-wrote the code to make it easier to maintain

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
Dim D As Integer, E As Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double
Dim RowCount As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

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

' Setup Output Headings
Range("B2").Value = "Text"
Range("B3").Value = "Distribution"
Range("C3").Value = "Combinations"
Range("D3").Value = "Percent"

' Format Output Headings
Range("B2").HorizontalAlignment = xlCenter
Range("B2").Font.FontStyle = "Bold"
Range("B2").Font.ColorIndex = 2

RowCount = 4
For i = MinDist To MaxDist

' Calculate Output
Cells(RowCount, "B").Value = i
Cells(RowCount, "C").Value = DistSum(i)
Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i)
' Format Output
Cells(RowCount, "C").NumberFormat = "##,###,##0"
Cells(RowCount, "D").NumberFormat = "##0.00"
RowCount = RowCount + 1
Next i

' Setup Totals
Cells(RowCount, "B").Value = "Totals"
Cells(RowCount, "C").Formula = _
"=Sum(C4:C" & (RowCount - 1) & ")"
Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value
Cells(RowCount, "D").Formula = _
"=Sum(D4:D" & (RowCount - 1) & ")"
Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value

' Format Totals
Cells(RowCount, "C").NumberFormat = "#,###,##0"
Cells(RowCount, "C").NumberFormat = "##0.00"

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

"Paul Black" wrote:
Hi Joel,

My original code in my first post works OK except it starts the output
in the wrong place. It should start with 21 directly after the titles
Distribution, Combinations & Percent and continue down until it
reaches 279. Then it should produce the totals for combinations and
percent directly under that.
How would you write the code to do exactly what I have done. Would you
NOT use the .Offset(i + 1, 0) etc at all?.
I am new to VBA so my programming is not the best.
I used the Activecell.Offset because there is a lot of formatting in
the original and this is the only way I know how to do it.

Thanks in Advance.
All the Best.
Paul

On Oct 10, 5:05 pm, Joel wrote:
Paul: I want to make sure I solvig the right problem. Be patient with me, I
don't want to make any wrong assuptions. Please answer these questions. I
don't like using active cell just for the reason we are havving here. my
preference is to use code looke this

For I = MinDist To MaxDist
Range("B20").offset(I - MinDist,0).Value = i
Range("B20").Offset(I - MinDist, 1).Value = nSum(i)
Next I

The new code you said works has this line of code.

ActiveCell.Offset(1, 0).Select

The abbove line is moving the active cell down the worksheet. You original
code did not have this line.

I look at your original posting and notice one additional item I did not
notice before. You are using the variable i outside the for loop when you
execute the code after " Setup Totals". This willincrement i one number
larger than you would expect.

For i = 1 to 5
'for loop code
next i

a = i

The results of the above code is that a = 6 (not 5 like you would expect).

1) The second code when it fails, why does it fails?
a) The data is being written into the wrong cells
b) The data is wrong and the cell locations are correct
c) Both the data is wrong and the cell locations wrong

"Paul Black" wrote:
Thanks for the reply Joel,

My main request is for it to work using my original code which
includes the .Offset and i + 1.
The second code I posted works perfectly but I don't really know why,
because there is not a big difference between the two codes.
My original code is ...

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

.... which I have tried to adapt with your suggestions but to no avail.

Thanks for your time and effort on this, it is appreciated.
All the Best.
Paul

On Oct 10, 3:29 pm, Joel wrote:
I don't know what your last change was but you need to make a the following
change (add in your last change)

from

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

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

I hope this helps. Problem with the postings at this site you can't always
tell how well people understand VBA. Some postings the people are expert and
some are novices and are using other peoples code that they don't understand
how the code actually works. I don't know your level of expertise and are
trying to give my answers at a level you would understand. Sorry if I'm
giving too little or too much information.

"Paul Black" wrote:
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