Gap between Headings and Start of Output
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
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 -
|