Gap between Headings and Start of Output

[Paul Black]

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

 

[Guest]

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]

Hi Joel,

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

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)

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

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 -

 

[Guest]

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

Hi Joel,

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

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)

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

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 -

 

[Paul Black]

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

Hi Joel,

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

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)

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 -

 

[Guest]

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

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

Hi Joel,

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

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)

:
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

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 -

 

[Paul Black]

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

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

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

Hi Joel,
I get a runtime error 9, subscript out of range.
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)
:
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 -

 

[Guest]

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.

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

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

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

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 <[email protected]> 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)

:
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

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 -

 

[Paul Black]

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

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.

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

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
:
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
Hi Joel,
I get a runtime error 9, subscript out of range.
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)
:
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 -- Hide quoted text -

- Show quoted text -

 

[Guest]

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

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

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.

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

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

:
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

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 <[email protected]> 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)

:
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

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 -- Hide quoted text -

- Show quoted text -

 

[Paul Black]

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

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

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

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.
:
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
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
:
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
Hi Joel,
I get a runtime error 9, subscript out of range.
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)
:
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 -

...

read more »- Hide quoted text -

- Show quoted text -

 

[Guest]

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" & (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

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

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

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

I don't know what your last change was but you need to make a the following
change (add in your last change)

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.

:
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

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

:
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

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 <[email protected]> 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)

:
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

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

 

[Paul Black]

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

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" & (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

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

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
:
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
I don't know what your last change was but you need to make a thefollowing
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'tunderstand
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. Sorryif I'm
giving too little or too much information.
:
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
Erase your worksheet and run the code again. I think you aregetting 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
:
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
Hi Joel,
I get a runtime error 9, subscript out of range.
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)
:
Hi everyone,

...

read more »- Hide quoted text -

- Show quoted text -

 

[Guest]

change your dim to the line below and add redim.

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

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

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" & (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

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

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

:
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

I don't know what your last change was but you need to make a the following
change (add in your last change)

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.

:
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

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

:
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

 

[Paul Black]

Thanks Joel,

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

Thanks in Advance.
All the Best.
Paul

change your dim to the line below and add redim.

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

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

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" & (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
:
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
Paul: I want to make sure I solvig the right problem. Be patientwith 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 loopwhen 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
:
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
I don't know what your last change was but you need to make athe 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.
:
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
Erase your worksheet and run the code again. I think youare 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
:
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

...

read more »- Hide quoted text -

- Show quoted text -

 

[Paul Black]

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

Thanks Joel,

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

Thanks in Advance.
All the Best.
Paul

change your dim to the line below and add redim.

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

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
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" & (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
:
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 formattingin
the original and this is the only way I know how to do it.
Thanks in Advance.
All the Best.
Paul
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 havvinghere. 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 ione 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
:
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 tono avail.
Thanks for your time and effort on this, it is appreciated.
All the Best.
Paul
I don't know what your last change was but you need to makea 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 theydon'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.
:
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
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
:
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

...

read more »- Hide quoted text -

- Show quoted text -

 

[Guest]

the largest the array value will be is when A - F are at there highest value
this equals

DistSum(A + B + C + D + E + F)
(MaxBall - 5) + (MaxBall - 4) + (MaxBall - 3) + (MaxBall - 2) + (MaxBall -
1) + MaxBall

The abovve equation can be simplified to
(6 * MaxBall) - 15

Thanks Joel,

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

Thanks in Advance.
All the Best.
Paul

change your dim to the line below and add redim.

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

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

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" & (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

:
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

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

:
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

I don't know what your last change was but you need to make a the following
change (add in your last change)

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.

:
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

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

:
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

...

read more ;- Hide quoted text -

- Show quoted text -

 

[Guest]

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

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

Thanks Joel,

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

Thanks in Advance.
All the Best.
Paul

change your dim to the line below and add redim.

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

:
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

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" & (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

:
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

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

:
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

I don't know what your last change was but you need to make a the following
change (add in your last change)

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.

:
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

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

:
Hi Joel,

Why does this work ...

Option Explicit
Option Base 1

 

[Paul Black]

Hi Joel,

Sorry to trouble you. The code ...

Option Explicit
Option Base 1

Const MinSum As Integer = 21
Const MaxSum As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Sum()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim CombSum() As Single
Dim RowCount As Integer
ReDim CombSum((6 * MaxBall) - 15)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

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

CombSum(A + B + C + D + E + F) = CombSum(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 = "Sum"
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 = MinSum To MaxSum
' Calculate Output
Cells(RowCount, "B").Value = i
Cells(RowCount, "C").Value = CombSum(i)
Cells(RowCount, "D").Value = 100 / TotalComb * CombSum(i)

' Format Output
Cells(RowCount, "B").HorizontalAlignment = xlLeft
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" & (RowCount - 1) & ")"
Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value

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

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

.... works brilliantly, thank you.
I have a list of draws in a worksheet named "Draws". The 6 number
combinations go from cells "B4:G?". The "G?" will obviously change on
a regular basis as more draws are added.
What I would ideally like is to SUM each 6 number combination in the
sheet named "Draws". I would then like the SUM totals to go in column
"H" next to the SUM total in column "A" produced by the program above
please.
I don't know if this will need a seperate Sub or an amendment to the
one above.

Thanks in Advance.
All the Best.
Paul

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

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

Thanks Joel,
The program now at works perfectly, thank you, but why?.
Thanks in Advance.
All the Best.
Paul
change your dim to the line below and add redim.
Dim DistSum() As Single
ReDim DistSum((6 * MaxBall) - 15)
:
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 haveto
change much each time thanks to you.
Thanks in Advance.
All the Best.
Paul
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" & (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
:
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
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
:
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 <[email protected]>wrote:

...

read more »- Hide quoted text -

- Show quoted text -

 

[Guest]

I think I understand what you want. I modified the code to add column H. I
added a constant Const LastDrawCol = "G" so you can easily change the last
column.

Option Explicit
Option Base 1

Const LastDrawCol = "G"
Const MinSum As Integer = 21
Const MaxSum As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816

Sub Sum()
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 CombSum() As Single
Dim RowCount As Integer
ReDim CombSum((6 * MaxBall) - 15)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

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

CombSum(A + B + C + D + E + F) = _
CombSum(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 = "Sum"
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 = MinSum To MaxSum
' Calculate Output
Cells(RowCount, "B").Value = i
Cells(RowCount, "C").Value = CombSum(i)
Cells(RowCount, "D").Value = 100 / TotalComb * CombSum(i)
Cells(RowCount, "H").Formula = "=Sum(Draws!B" & RowCount & _
":" & LastDrawCol & RowCount & ")"
' Format Output
Cells(RowCount, "B").HorizontalAlignment = xlLeft
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" & (RowCount - 1) & ")"
Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value

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

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