Gap between Headings and Start of Output
I re-wrote the code to make it easier to maintain
Option Explicit
Option Base 1
Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816
Sub Test()
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double
Dim RowCount As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = MinDist To MaxDist
DistSum(i) = 0
Next i
For A = MinBall To MaxBall - 5
For B = A + 1 To MaxBall - 4
For C = B + 1 To MaxBall - 3
For D = C + 1 To MaxBall - 2
For E = D + 1 To MaxBall - 1
For F = E + 1 To MaxBall
DistSum(A + B + C + D + E + F) = _
DistSum(A + B + C + D + E + F) + 1
Next F
Next E
Next D
Next C
Next B
Next A
' Setup Output Headings
Range("B2").Value = "Text"
Range("B3").Value = "Distribution"
Range("C3").Value = "Combinations"
Range("D3").Value = "Percent"
' Format Output Headings
Range("B2").HorizontalAlignment = xlCenter
Range("B2").Font.FontStyle = "Bold"
Range("B2").Font.ColorIndex = 2
RowCount = 4
For i = MinDist To MaxDist
' Calculate Output
Cells(RowCount, "B").Value = i
Cells(RowCount, "C").Value = DistSum(i)
Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i)
' Format Output
Cells(RowCount, "C").NumberFormat = "##,###,##0"
Cells(RowCount, "D").NumberFormat = "##0.00"
RowCount = RowCount + 1
Next i
' Setup Totals
Cells(RowCount, "B").Value = "Totals"
Cells(RowCount, "C").Formula = _
"=Sum(C4:C" & (RowCount - 1) & ")"
Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value
Cells(RowCount, "D").Formula = _
"=Sum(D4:D" & (RowCount - 1) & ")"
Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value
' Format Totals
Cells(RowCount, "C").NumberFormat = "#,###,##0"
Cells(RowCount, "C").NumberFormat = "##0.00"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
"Paul Black" wrote:
Hi Joel,
My original code in my first post works OK except it starts the output
in the wrong place. It should start with 21 directly after the titles
Distribution, Combinations & Percent and continue down until it
reaches 279. Then it should produce the totals for combinations and
percent directly under that.
How would you write the code to do exactly what I have done. Would you
NOT use the .Offset(i + 1, 0) etc at all?.
I am new to VBA so my programming is not the best.
I used the Activecell.Offset because there is a lot of formatting in
the original and this is the only way I know how to do it.
Thanks in Advance.
All the Best.
Paul
On Oct 10, 5:05 pm, Joel wrote:
Paul: I want to make sure I solvig the right problem. Be patient with me, I
don't want to make any wrong assuptions. Please answer these questions. I
don't like using active cell just for the reason we are havving here. my
preference is to use code looke this
For I = MinDist To MaxDist
Range("B20").offset(I - MinDist,0).Value = i
Range("B20").Offset(I - MinDist, 1).Value = nSum(i)
Next I
The new code you said works has this line of code.
ActiveCell.Offset(1, 0).Select
The abbove line is moving the active cell down the worksheet. You original
code did not have this line.
I look at your original posting and notice one additional item I did not
notice before. You are using the variable i outside the for loop when you
execute the code after " Setup Totals". This willincrement i one number
larger than you would expect.
For i = 1 to 5
'for loop code
next i
a = i
The results of the above code is that a = 6 (not 5 like you would expect).
1) The second code when it fails, why does it fails?
a) The data is being written into the wrong cells
b) The data is wrong and the cell locations are correct
c) Both the data is wrong and the cell locations wrong
"Paul Black" wrote:
Thanks for the reply Joel,
My main request is for it to work using my original code which
includes the .Offset and i + 1.
The second code I posted works perfectly but I don't really know why,
because there is not a big difference between the two codes.
My original code is ...
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i
' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value
.... which I have tried to adapt with your suggestions but to no avail.
Thanks for your time and effort on this, it is appreciated.
All the Best.
Paul
On Oct 10, 3:29 pm, Joel wrote:
I don't know what your last change was but you need to make a the following
change (add in your last change)
from
For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I
For I = MinDist To MaxDist
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I
I hope this helps. Problem with the postings at this site you can't always
tell how well people understand VBA. Some postings the people are expert and
some are novices and are using other peoples code that they don't understand
how the code actually works. I don't know your level of expertise and are
trying to give my answers at a level you would understand. Sorry if I'm
giving too little or too much information.
"Paul Black" wrote:
Hi Joel,
I did as you suggested and the second code worked perfectly.
I really want my original code to work though please.
Thanks in Advance.
All the Best.
Paul
On Oct 10, 2:02 pm, Joel wrote:
Erase your worksheet and run the code again. I think you are getting fooled
that the code you posted works.
this code keeps on writing to the same cell. It doesn't increment the row
counter
For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I
"Paul Black" wrote:
Hi Joel,
Why does this work ...
Option Explicit
Option Base 1
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim I As Integer, nSum(279) As Long
Sub SumAll()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("B2").Select
For i = 21 To 279
nSum(i) = 0
Next i
For A = 1 To 44
For B = A + 1 To 45
For C = B + 1 To 46
For D = C + 1 To 47
For E = D + 1 To 48
For F = E + 1 To 49
nSum(A + B + C + D + E + F) = nSum(A + B + C + D + E + F)
+ 1
Next F
Next E
Next D
Next C
Next B
Next A
For I = 21 To 279
ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = nSum(i)
ActiveCell.Offset(1, 0).Select
Next I
Application.ScreenUpdating = False
Range("A1").Select
End Sub
.... and my one doesn't.
Is it the fact that I use "i + 1" or something. Is using "i + 1" the
right thing to use.
Also, when I ran your code it started at 0 and went to 259, when it
should have started at 21 to 279.
Thanks in Advance.
All the Best.
Paul
On Oct 10, 1:22 pm, Paul Black wrote:
Hi Joel,
I get a runtime error 9, subscript out of range.
Thanks in Advance.
All the Best.
Paul
On Oct 10, 1:04 pm, Joel wrote:
Make the sime change below in the for loop
from
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
to
For i = 0 to ( MaxDist - MinDist + 1)
"Paul Black" wrote:
Hi everyone,
I have the following which works great except that there is a gap
between the headings and the start of the output. The ouput should be
from 21 to 279 and start directly underneath the headings.. Here is the
code :-
Option Explicit
Option Base 1
Const MinDist As Integer = 21
Const MaxDist As Integer = 279
Const MinBall As Integer = 1
Const MaxBall As Integer = 49
Const TotalComb As Long = 13983816
Sub Test()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As
Integer, F As Integer
Dim i As Integer
Dim DistSum(279) As Double
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("B2").Select
For i = MinDist To MaxDist
DistSum(i) = 0
Next i
For A = MinBall To MaxBall - 5
For B = A + 1 To MaxBall - 4
For C = B + 1 To MaxBall - 3
For D = C + 1 To MaxBall - 2
For E = D + 1 To MaxBall - 1
For F = E + 1 To MaxBall
DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D +
E + F) + 1
Next F
Next E
Next D
Next C
Next B
Next A
With ActiveCell
' Setup Output Headings
.Offset(0, 0).Value = "Text"
.Offset(1, 0).Value = "Distribution"
.Offset(1, 1).Value = "Combinations"
.Offset(1, 2).Value = "Percent"
' Format Output Headings
.Offset(0, 0).HorizontalAlignment = xlCenter
.Offset(0, 0).Font.FontStyle = "Bold"
.Offset(0, 0).Font.ColorIndex = 2
For i = MinDist To MaxDist
' Calculate Output
.Offset(i + 1, 0).Value = i
.Offset(i + 1, 1).Value = DistSum(i)
.Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i)
' Format Output
.Offset(i + 1, 1).NumberFormat = "##,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
Next i
' Setup Totals
.Offset(i + 1, 0).Value = "Totals"
.Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)"
.Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value
.Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)"
.Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value
' Format Totals
.Offset(i + 1, 1).NumberFormat = "#,###,##0"
.Offset(i + 1, 2).NumberFormat = "##0.00"
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
|