Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
I missed the statement Option Base 1 which says you are stating you arrays at
one not zero. You are getting the error because the code is referecing index zero in the array For i = 1 to ( MaxDist - MinDist + 1) If you still get a one row gap then you need to make the additional change on all the writes to cells in this for loop. from ..Offset(i + 1, 0).Value = i to ..Offset(i, 0).Value = i "Paul Black" wrote: Hi Joel, I get a runtime error 9, subscript out of range. Thanks in Advance. All the Best. Paul On Oct 10, 1:04 pm, Joel wrote: Make the sime change below in the for loop from For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) to For i = 0 to ( MaxDist - MinDist + 1) "Paul Black" wrote: Hi everyone, I have the following which works great except that there is a gap between the headings and the start of the output. The ouput should be from 21 to 279 and start directly underneath the headings. Here is the code :- Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Range("B2").Select For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A With ActiveCell ' Setup Output Headings .Offset(0, 0).Value = "Text" .Offset(1, 0).Value = "Distribution" .Offset(1, 1).Value = "Combinations" .Offset(1, 2).Value = "Percent" ' Format Output Headings .Offset(0, 0).HorizontalAlignment = xlCenter .Offset(0, 0).Font.FontStyle = "Bold" .Offset(0, 0).Font.ColorIndex = 2 For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) ' Format Output .Offset(i + 1, 1).NumberFormat = "##,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" Next i ' Setup Totals .Offset(i + 1, 0).Value = "Totals" .Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)" .Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value .Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)" .Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value ' Format Totals .Offset(i + 1, 1).NumberFormat = "#,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Any help will be greatly appreciated. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 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 - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 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 - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
Thanks for the re-written code Joel, it is appreciated.
One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Paul Black" wrote: Hi Joel, My original code in my first post works OK except it starts the output in the wrong place. It should start with 21 directly after the titles Distribution, Combinations & Percent and continue down until it reaches 279. Then it should produce the totals for combinations and percent directly under that. How would you write the code to do exactly what I have done. Would you NOT use the .Offset(i + 1, 0) etc at all?. I am new to VBA so my programming is not the best. I used the Activecell.Offset because there is a lot of formatting in the original and this is the only way I know how to do it. Thanks in Advance. All the Best. Paul On Oct 10, 5:05 pm, Joel wrote: Paul: I want to make sure I solvig the right problem. Be patient with me, I don't want to make any wrong assuptions. Please answer these questions. I don't like using active cell just for the reason we are havving here. my preference is to use code looke this For I = MinDist To MaxDist Range("B20").offset(I - MinDist,0).Value = i Range("B20").Offset(I - MinDist, 1).Value = nSum(i) Next I The new code you said works has this line of code. ActiveCell.Offset(1, 0).Select The abbove line is moving the active cell down the worksheet. You original code did not have this line. I look at your original posting and notice one additional item I did not notice before. You are using the variable i outside the for loop when you execute the code after " Setup Totals". This willincrement i one number larger than you would expect. For i = 1 to 5 'for loop code next i a = i The results of the above code is that a = 6 (not 5 like you would expect). 1) The second code when it fails, why does it fails? a) The data is being written into the wrong cells b) The data is wrong and the cell locations are correct c) Both the data is wrong and the cell locations wrong "Paul Black" wrote: Thanks for the reply Joel, My main request is for it to work using my original code which includes the .Offset and i + 1. The second code I posted works perfectly but I don't really know why, because there is not a big difference between the two codes. My original code is ... For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) ' Format Output .Offset(i + 1, 1).NumberFormat = "##,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" Next i ' Setup Totals .Offset(i + 1, 0).Value = "Totals" .Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)" .Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value .Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)" .Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value .... which I have tried to adapt with your suggestions but to no avail. Thanks for your time and effort on this, it is appreciated. All the Best. Paul On Oct 10, 3:29 pm, Joel wrote: I don't know what your last change was but you need to make a the following change (add in your last change) from For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I For I = MinDist To MaxDist ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I I hope this helps. Problem with the postings at this site you can't always tell how well people understand VBA. Some postings the people are expert and some are novices and are using other peoples code that they don't understand how the code actually works. I don't know your level of expertise and are trying to give my answers at a level you would understand. Sorry if I'm giving too little or too much information. "Paul Black" wrote: Hi Joel, I did as you suggested and the second code worked perfectly. I really want my original code to work though please. Thanks in Advance. All the Best. Paul On Oct 10, 2:02 pm, Joel wrote: Erase your worksheet and run the code again. I think you are getting fooled that the code you posted works. this code keeps on writing to the same cell. It doesn't increment the row counter For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I "Paul Black" wrote: Hi Joel, Why does this work ... Option Explicit Option Base 1 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, ... read more »- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
change your dim to the line below and add redim.
Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "Paul Black" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Paul Black" wrote: Hi Joel, My original code in my first post works OK except it starts the output in the wrong place. It should start with 21 directly after the titles Distribution, Combinations & Percent and continue down until it reaches 279. Then it should produce the totals for combinations and percent directly under that. How would you write the code to do exactly what I have done. Would you NOT use the .Offset(i + 1, 0) etc at all?. I am new to VBA so my programming is not the best. I used the Activecell.Offset because there is a lot of formatting in the original and this is the only way I know how to do it. Thanks in Advance. All the Best. Paul On Oct 10, 5:05 pm, Joel wrote: Paul: I want to make sure I solvig the right problem. Be patient with me, I don't want to make any wrong assuptions. Please answer these questions. I don't like using active cell just for the reason we are havving here. my preference is to use code looke this For I = MinDist To MaxDist Range("B20").offset(I - MinDist,0).Value = i Range("B20").Offset(I - MinDist, 1).Value = nSum(i) Next I The new code you said works has this line of code. ActiveCell.Offset(1, 0).Select The abbove line is moving the active cell down the worksheet. You original code did not have this line. I look at your original posting and notice one additional item I did not notice before. You are using the variable i outside the for loop when you execute the code after " Setup Totals". This willincrement i one number larger than you would expect. For i = 1 to 5 'for loop code next i a = i The results of the above code is that a = 6 (not 5 like you would expect). 1) The second code when it fails, why does it fails? a) The data is being written into the wrong cells b) The data is wrong and the cell locations are correct c) Both the data is wrong and the cell locations wrong "Paul Black" wrote: Thanks for the reply Joel, My main request is for it to work using my original code which includes the .Offset and i + 1. The second code I posted works perfectly but I don't really know why, because there is not a big difference between the two codes. My original code is ... For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) ' Format Output .Offset(i + 1, 1).NumberFormat = "##,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" Next i ' Setup Totals .Offset(i + 1, 0).Value = "Totals" .Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)" .Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value .Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)" .Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value .... which I have tried to adapt with your suggestions but to no avail. Thanks for your time and effort on this, it is appreciated. All the Best. Paul On Oct 10, 3:29 pm, Joel wrote: I don't know what your last change was but you need to make a the following change (add in your last change) from For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I For I = MinDist To MaxDist ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I I hope this helps. Problem with the postings at this site you can't always tell how well people understand VBA. Some postings the people are expert and some are novices and are using other peoples code that they don't understand how the code actually works. I don't know your level of expertise and are trying to give my answers at a level you would understand. Sorry if I'm giving too little or too much information. "Paul Black" wrote: Hi Joel, I did as you suggested and the second code worked perfectly. I really want my original code to work though please. Thanks in Advance. All the Best. Paul On Oct 10, 2:02 pm, Joel wrote: Erase your worksheet and run the code again. I think you are getting fooled that the code you posted works. this code keeps on writing to the same cell. It doesn't increment the row counter For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I "Paul Black" wrote: Hi Joel, Why does this work ... Option Explicit Option Base 1 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
Thanks Joel,
The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "Paul Black" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Paul Black" wrote: Hi Joel, My original code in my first post works OK except it starts the output in the wrong place. It should start with 21 directly after the titles Distribution, Combinations & Percent and continue down until it reaches 279. Then it should produce the totals for combinations and percent directly under that. How would you write the code to do exactly what I have done. Would you NOT use the .Offset(i + 1, 0) etc at all?. I am new to VBA so my programming is not the best. I used the Activecell.Offset because there is a lot of formatting in the original and this is the only way I know how to do it. Thanks in Advance. All the Best. Paul On Oct 10, 5:05 pm, Joel wrote: Paul: I want to make sure I solvig the right problem. Be patient with me, I don't want to make any wrong assuptions. Please answer these questions. I don't like using active cell just for the reason we are havving here. my preference is to use code looke this For I = MinDist To MaxDist Range("B20").offset(I - MinDist,0).Value = i Range("B20").Offset(I - MinDist, 1).Value = nSum(i) Next I The new code you said works has this line of code. ActiveCell.Offset(1, 0).Select The abbove line is moving the active cell down the worksheet. You original code did not have this line. I look at your original posting and notice one additional item I did not notice before. You are using the variable i outside the for loop when you execute the code after " Setup Totals". This willincrement i one number larger than you would expect. For i = 1 to 5 'for loop code next i a = i The results of the above code is that a = 6 (not 5 like you would expect). 1) The second code when it fails, why does it fails? a) The data is being written into the wrong cells b) The data is wrong and the cell locations are correct c) Both the data is wrong and the cell locations wrong "Paul Black" wrote: Thanks for the reply Joel, My main request is for it to work using my original code which includes the .Offset and i + 1. The second code I posted works perfectly but I don't really know why, because there is not a big difference between the two codes. My original code is ... For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) ' Format Output .Offset(i + 1, 1).NumberFormat = "##,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" Next i ' Setup Totals .Offset(i + 1, 0).Value = "Totals" .Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)" .Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value .Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)" .Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value .... which I have tried to adapt with your suggestions but to no avail. Thanks for your time and effort on this, it is appreciated. All the Best. Paul On Oct 10, 3:29 pm, Joel wrote: I don't know what your last change was but you need to make a the following change (add in your last change) from For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I For I = MinDist To MaxDist ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I I hope this helps. Problem with the postings at this site you can't always tell how well people understand VBA. Some postings the people are expert and some are novices and are using other peoples code that they don't understand how the code actually works. I don't know your level of expertise and are trying to give my answers at a level you would understand. Sorry if I'm giving too little or too much information. "Paul Black" wrote: Hi Joel, I did as you suggested and the second code worked perfectly. I really want my original code to work though please. Thanks in Advance. All the Best. Paul On Oct 10, 2:02 pm, Joel wrote: Erase your worksheet and run the code again. I think you are getting fooled that the code you posted works. this code keeps on writing to the same cell. It doesn't increment the row counter For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I "Paul Black" wrote: Hi Joel, Why does this work ... Option Explicit Option Base 1 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 - |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
Hi Joel,
One FINAL question, I promise. If I wanted to physically input values into cells for example, would ... With Range("B4") ' Setup Distribution Categories .Offset(0, 0).Value = "111111" .Offset(1, 0).Value = "211110" .Offset(2, 0).Value = "221100" .Offset(3, 0).Value = "222000" .Offset(4, 0).Value = "311100" .Offset(5, 0).Value = "321000" .Offset(6, 0).Value = "330000" .Offset(7, 0).Value = "411000" .Offset(8, 0).Value = "420000" .Offset(9, 0).Value = "510000" .Offset(10, 0).Value = "600000" ' Format Distribution Categories .Resize(11, 1).HorizontalAlignment = xlLeft End With .... be the right way to do it. Thanks in Advance. All the Best. Paul On Oct 11, 12:21 pm, Paul Black wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "Paul Black" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Paul Black" wrote: Hi Joel, My original code in my first post works OK except it starts the output in the wrong place. It should start with 21 directly after the titles Distribution, Combinations & Percent and continue down until it reaches 279. Then it should produce the totals for combinations and percent directly under that. How would you write the code to do exactly what I have done. Would you NOT use the .Offset(i + 1, 0) etc at all?. I am new to VBA so my programming is not the best. I used the Activecell.Offset because there is a lot of formatting in the original and this is the only way I know how to do it. Thanks in Advance. All the Best. Paul On Oct 10, 5:05 pm, Joel wrote: Paul: I want to make sure I solvig the right problem. Be patient with me, I don't want to make any wrong assuptions. Please answer these questions. I don't like using active cell just for the reason we are havving here. my preference is to use code looke this For I = MinDist To MaxDist Range("B20").offset(I - MinDist,0).Value = i Range("B20").Offset(I - MinDist, 1).Value = nSum(i) Next I The new code you said works has this line of code. ActiveCell.Offset(1, 0).Select The abbove line is moving the active cell down the worksheet. You original code did not have this line. I look at your original posting and notice one additional item I did not notice before. You are using the variable i outside the for loop when you execute the code after " Setup Totals". This willincrement i one number larger than you would expect. For i = 1 to 5 'for loop code next i a = i The results of the above code is that a = 6 (not 5 like you would expect). 1) The second code when it fails, why does it fails? a) The data is being written into the wrong cells b) The data is wrong and the cell locations are correct c) Both the data is wrong and the cell locations wrong "Paul Black" wrote: Thanks for the reply Joel, My main request is for it to work using my original code which includes the .Offset and i + 1. The second code I posted works perfectly but I don't really know why, because there is not a big difference between the two codes. My original code is ... For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) ' Format Output .Offset(i + 1, 1).NumberFormat = "##,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" Next i ' Setup Totals .Offset(i + 1, 0).Value = "Totals" .Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)" .Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value .Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)" .Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value .... which I have tried to adapt with your suggestions but to no avail. Thanks for your time and effort on this, it is appreciated. All the Best. Paul On Oct 10, 3:29 pm, Joel wrote: I don't know what your last change was but you need to make a the following change (add in your last change) from For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I For I = MinDist To MaxDist ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I I hope this helps. Problem with the postings at this site you can't always tell how well people understand VBA. Some postings the people are expert and some are novices and are using other peoples code that they don't understand how the code actually works. I don't know your level of expertise and are trying to give my answers at a level you would understand. Sorry if I'm giving too little or too much information. "Paul Black" wrote: Hi Joel, I did as you suggested and the second code worked perfectly. I really want my original code to work though please. Thanks in Advance. All the Best. Paul On Oct 10, 2:02 pm, Joel wrote: Erase your worksheet and run the code again. I think you are getting fooled that the code you posted works. this code keeps on writing to the same cell. It doesn't increment the row counter For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I "Paul Black" wrote: Hi Joel, Why does this work ... Option Explicit Option Base 1 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 - |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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 "Paul Black" wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "Paul Black" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Paul Black" wrote: Hi Joel, My original code in my first post works OK except it starts the output in the wrong place. It should start with 21 directly after the titles Distribution, Combinations & Percent and continue down until it reaches 279. Then it should produce the totals for combinations and percent directly under that. How would you write the code to do exactly what I have done. Would you NOT use the .Offset(i + 1, 0) etc at all?. I am new to VBA so my programming is not the best. I used the Activecell.Offset because there is a lot of formatting in the original and this is the only way I know how to do it. Thanks in Advance. All the Best. Paul On Oct 10, 5:05 pm, Joel wrote: Paul: I want to make sure I solvig the right problem. Be patient with me, I don't want to make any wrong assuptions. Please answer these questions. I don't like using active cell just for the reason we are havving here. my preference is to use code looke this For I = MinDist To MaxDist Range("B20").offset(I - MinDist,0).Value = i Range("B20").Offset(I - MinDist, 1).Value = nSum(i) Next I The new code you said works has this line of code. ActiveCell.Offset(1, 0).Select The abbove line is moving the active cell down the worksheet. You original code did not have this line. I look at your original posting and notice one additional item I did not notice before. You are using the variable i outside the for loop when you execute the code after " Setup Totals". This willincrement i one number larger than you would expect. For i = 1 to 5 'for loop code next i a = i The results of the above code is that a = 6 (not 5 like you would expect). 1) The second code when it fails, why does it fails? a) The data is being written into the wrong cells b) The data is wrong and the cell locations are correct c) Both the data is wrong and the cell locations wrong "Paul Black" wrote: Thanks for the reply Joel, My main request is for it to work using my original code which includes the .Offset and i + 1. The second code I posted works perfectly but I don't really know why, because there is not a big difference between the two codes. My original code is ... For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) ' Format Output .Offset(i + 1, 1).NumberFormat = "##,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" Next i ' Setup Totals .Offset(i + 1, 0).Value = "Totals" .Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)" .Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value .Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)" .Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value .... which I have tried to adapt with your suggestions but to no avail. Thanks for your time and effort on this, it is appreciated. All the Best. Paul On Oct 10, 3:29 pm, Joel wrote: I don't know what your last change was but you need to make a the following change (add in your last change) from For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I For I = MinDist To MaxDist ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I I hope this helps. Problem with the postings at this site you can't always tell how well people understand VBA. Some postings the people are expert and some are novices and are using other peoples code that they don't understand how the code actually works. I don't know your level of expertise and are trying to give my answers at a level you would understand. Sorry if I'm giving too little or too much information. "Paul Black" wrote: Hi Joel, I did as you suggested and the second code worked perfectly. I really want my original code to work though please. Thanks in Advance. All the Best. Paul On Oct 10, 2:02 pm, Joel wrote: Erase your worksheet and run the code again. I think you are getting fooled that the code you posted works. this code keeps on writing to the same cell. It doesn't increment the row counter For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I "Paul Black" wrote: Hi Joel, Why does this work ... Option Explicit Option Base 1 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 - |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
Your code works and is very flexible because you just have to change B4 to
another cell and all the locations change. My prefference is to avoid ..offset because it is harder to figure out where items are going. I would of done the code below, but both are acceptable. ' Setup Distribution Categories Range("B4").Value = "111111" Range("B5").Value = "211110" Range("B6").Value = "221100" Range("B7").Value = "222000" Range("B8").Value = "311100" Range("B9").Value = "321000" Range("B10").Value = "330000" Range("B11").Value = "411000" Range("B12").Value = "420000" Range("B13").Value = "510000" Range("B14").Value = "600000" ' Format Distribution Categories Range("B4:B14").HorizontalAlignment = xlLeft "Paul Black" wrote: Hi Joel, One FINAL question, I promise. If I wanted to physically input values into cells for example, would ... With Range("B4") ' Setup Distribution Categories .Offset(0, 0).Value = "111111" .Offset(1, 0).Value = "211110" .Offset(2, 0).Value = "221100" .Offset(3, 0).Value = "222000" .Offset(4, 0).Value = "311100" .Offset(5, 0).Value = "321000" .Offset(6, 0).Value = "330000" .Offset(7, 0).Value = "411000" .Offset(8, 0).Value = "420000" .Offset(9, 0).Value = "510000" .Offset(10, 0).Value = "600000" ' Format Distribution Categories .Resize(11, 1).HorizontalAlignment = xlLeft End With .... be the right way to do it. Thanks in Advance. All the Best. Paul On Oct 11, 12:21 pm, Paul Black wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "Paul Black" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "Paul Black" wrote: Hi Joel, My original code in my first post works OK except it starts the output in the wrong place. It should start with 21 directly after the titles Distribution, Combinations & Percent and continue down until it reaches 279. Then it should produce the totals for combinations and percent directly under that. How would you write the code to do exactly what I have done. Would you NOT use the .Offset(i + 1, 0) etc at all?. I am new to VBA so my programming is not the best. I used the Activecell.Offset because there is a lot of formatting in the original and this is the only way I know how to do it. Thanks in Advance. All the Best. Paul On Oct 10, 5:05 pm, Joel wrote: Paul: I want to make sure I solvig the right problem. Be patient with me, I don't want to make any wrong assuptions. Please answer these questions. I don't like using active cell just for the reason we are havving here. my preference is to use code looke this For I = MinDist To MaxDist Range("B20").offset(I - MinDist,0).Value = i Range("B20").Offset(I - MinDist, 1).Value = nSum(i) Next I The new code you said works has this line of code. ActiveCell.Offset(1, 0).Select The abbove line is moving the active cell down the worksheet. You original code did not have this line. I look at your original posting and notice one additional item I did not notice before. You are using the variable i outside the for loop when you execute the code after " Setup Totals". This willincrement i one number larger than you would expect. For i = 1 to 5 'for loop code next i a = i The results of the above code is that a = 6 (not 5 like you would expect). 1) The second code when it fails, why does it fails? a) The data is being written into the wrong cells b) The data is wrong and the cell locations are correct c) Both the data is wrong and the cell locations wrong "Paul Black" wrote: Thanks for the reply Joel, My main request is for it to work using my original code which includes the .Offset and i + 1. The second code I posted works perfectly but I don't really know why, because there is not a big difference between the two codes. My original code is ... For i = MinDist To MaxDist ' Calculate Output .Offset(i + 1, 0).Value = i .Offset(i + 1, 1).Value = DistSum(i) .Offset(i + 1, 2).Value = 100 / TotalComb * DistSum(i) ' Format Output .Offset(i + 1, 1).NumberFormat = "##,###,##0" .Offset(i + 1, 2).NumberFormat = "##0.00" Next i ' Setup Totals .Offset(i + 1, 0).Value = "Totals" .Offset(i + 1, 1).FormulaR1C1 = "=Sum(R4C3:R[-1]C)" .Offset(i + 1, 1).Formula = .Offset(i + 1, 1).Value .Offset(i + 1, 2).FormulaR1C1 = "=Sum(R4C4:R[-1]C)" .Offset(i + 1, 2).Formula = .Offset(i + 1, 2).Value .... which I have tried to adapt with your suggestions but to no avail. Thanks for your time and effort on this, it is appreciated. All the Best. Paul On Oct 10, 3:29 pm, Joel wrote: I don't know what your last change was but you need to make a the following change (add in your last change) from For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I For I = MinDist To MaxDist ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I I hope this helps. Problem with the postings at this site you can't always tell how well people understand VBA. Some postings the people are expert and some are novices and are using other peoples code that they don't understand how the code actually works. I don't know your level of expertise and are trying to give my answers at a level you would understand. Sorry if I'm giving too little or too much information. "Paul Black" wrote: Hi Joel, I did as you suggested and the second code worked perfectly. I really want my original code to work though please. Thanks in Advance. All the Best. Paul On Oct 10, 2:02 pm, Joel wrote: Erase your worksheet and run the code again. I think you are getting fooled that the code you posted works. this code keeps on writing to the same cell. It doesn't increment the row counter For I = 21 To 279 ActiveCell.Value = i ActiveCell.Offset(0, 1).Value = nSum(i) ActiveCell.Offset(1, 0).Select Next I "Paul Black" wrote: Hi Joel, Why does this work ... Option Explicit Option Base 1 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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:D" & (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 On Oct 11, 1:10 pm, Joel wrote: 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 "PaulBlack" wrote: Hi Joel, One FINAL question, I promise. If I wanted to physically input values into cells for example, would ... With Range("B4") ' Setup Distribution Categories .Offset(0, 0).Value = "111111" .Offset(1, 0).Value = "211110" .Offset(2, 0).Value = "221100" .Offset(3, 0).Value = "222000" .Offset(4, 0).Value = "311100" .Offset(5, 0).Value = "321000" .Offset(6, 0).Value = "330000" .Offset(7, 0).Value = "411000" .Offset(8, 0).Value = "420000" .Offset(9, 0).Value = "510000" .Offset(10, 0).Value = "600000" ' Format Distribution Categories .Resize(11, 1).HorizontalAlignment = xlLeft End With .... be the right way to do it. Thanks in Advance. All the Best. Paul On Oct 11, 12:21 wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "PaulBlack" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "PaulBlack" 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 "PaulBlack" 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: ... read more »- Hide quoted text - - Show quoted text - |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
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:D" & (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 "Paul Black" wrote: 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:D" & (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 On Oct 11, 1:10 pm, Joel wrote: 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 "PaulBlack" wrote: Hi Joel, One FINAL question, I promise. If I wanted to physically input values into cells for example, would ... With Range("B4") ' Setup Distribution Categories .Offset(0, 0).Value = "111111" .Offset(1, 0).Value = "211110" .Offset(2, 0).Value = "221100" .Offset(3, 0).Value = "222000" .Offset(4, 0).Value = "311100" .Offset(5, 0).Value = "321000" .Offset(6, 0).Value = "330000" .Offset(7, 0).Value = "411000" .Offset(8, 0).Value = "420000" .Offset(9, 0).Value = "510000" .Offset(10, 0).Value = "600000" ' Format Distribution Categories .Resize(11, 1).HorizontalAlignment = xlLeft End With .... be the right way to do it. Thanks in Advance. All the Best. Paul On Oct 11, 12:21 wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "PaulBlack" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F Next E Next D Next C Next B Next A ' Setup Output Headings Range("B2").Value = "Text" Range("B3").Value = "Distribution" Range("C3").Value = "Combinations" Range("D3").Value = "Percent" ' Format Output Headings Range("B2").HorizontalAlignment = xlCenter Range("B2").Font.FontStyle = "Bold" Range("B2").Font.ColorIndex = 2 RowCount = 4 For i = MinDist To MaxDist ' Calculate Output Cells(RowCount, "B").Value = i Cells(RowCount, "C").Value = DistSum(i) Cells(RowCount, "D").Value = 100 / TotalComb * DistSum(i) ' Format Output Cells(RowCount, "C").NumberFormat = "##,###,##0" Cells(RowCount, "D").NumberFormat = "##0.00" RowCount = RowCount + 1 Next i ' Setup Totals Cells(RowCount, "B").Value = "Totals" Cells(RowCount, "C").Formula = _ "=Sum(C4:C" & (RowCount - 1) & ")" Cells(RowCount, "C").Formula = Cells(RowCount, "C").Value Cells(RowCount, "D").Formula = _ "=Sum(D4:D" & (RowCount - 1) & ")" Cells(RowCount, "D").Formula = Cells(RowCount, "D").Value ' Format Totals Cells(RowCount, "C").NumberFormat = "#,###,##0" Cells(RowCount, "C").NumberFormat = "##0.00" Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub "PaulBlack" 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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
Thanks for the reply Joel,
I think I confused things with my explanation. If I have the combinations ... 1 2 3 4 5 6 = 21 1 2 3 5 6 7 = 24 1 2 3 6 7 8 = 27 1 2 3 7 8 9 = 30 1 2 3 8 9 10 = 33 1 2 3 9 10 11 = 36 1 2 3 10 11 12 = 39 1 2 3 11 12 16 = 45 1 2 3 12 13 14 = 45 .... for example, the SUM total for 21 would equal 1 occasion. The SUM total for 45 would equal 2 occasions etc. Therefore the sum 21 would equal 1 and go in the same row as ... Cells(RowCount, "B").Value = i .... where the value 21 is. It is a count (the same as the existing program) of the number of times each SUM has occured and will go in column "H" on the same row as the corresponding number (i). If it has not occured then a zero will be entered. Thanks in Advance. All the Best. Paul On Oct 17, 11:39 am, Joel wrote: 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:D" & (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 "Paul Black" wrote: 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:D" & (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 On Oct 11, 1:10 pm, Joel wrote: 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 "PaulBlack" wrote: Hi Joel, One FINAL question, I promise. If I wanted to physically input values into cells for example, would ... With Range("B4") ' Setup Distribution Categories .Offset(0, 0).Value = "111111" .Offset(1, 0).Value = "211110" .Offset(2, 0).Value = "221100" .Offset(3, 0).Value = "222000" .Offset(4, 0).Value = "311100" .Offset(5, 0).Value = "321000" .Offset(6, 0).Value = "330000" .Offset(7, 0).Value = "411000" .Offset(8, 0).Value = "420000" .Offset(9, 0).Value = "510000" .Offset(10, 0).Value = "600000" ' Format Distribution Categories .Resize(11, 1).HorizontalAlignment = xlLeft End With .... be the right way to do it. Thanks in Advance. All the Best. Paul On Oct 11, 12:21 wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "PaulBlack" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain Option Explicit Option Base 1 Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Const MinBall As Integer = 1 Const MaxBall As Integer = 49 Const TotalComb As Long = 13983816 Sub Test() Dim A As Integer, B As Integer, C As Integer Dim D As Integer, E As Integer, F As Integer Dim i As Integer Dim DistSum(279) As Double Dim RowCount As Integer Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = MinDist To MaxDist DistSum(i) = 0 Next i For A = MinBall To MaxBall - 5 For B = A + 1 To MaxBall - 4 For C = B + 1 To MaxBall - 3 For D = C + 1 To MaxBall - 2 For E = D + 1 To MaxBall - 1 For F = E + 1 To MaxBall DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 Next F ... read more »- Hide quoted text - - Show quoted text - |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
Sorry Joel,
There will only be 6 number combinations, so the columns used will always be "B:G". It is the number of rows that will change. There could be 3,000 or so rows of draws to evaluate. As you know though the SUM totals can only be from 21 to 279. Thanks in Advance. All the Best. Paul On Oct 17, 11:56 am, Paul Black wrote: Thanks for the reply Joel, I think I confused things with my explanation. If I have the combinations ... 1 2 3 4 5 6 = 21 1 2 3 5 6 7 = 24 1 2 3 6 7 8 = 27 1 2 3 7 8 9 = 30 1 2 3 8 9 10 = 33 1 2 3 9 10 11 = 36 1 2 3 10 11 12 = 39 1 2 3 11 12 16 = 45 1 2 3 12 13 14 = 45 ... for example, the SUM total for 21 would equal 1 occasion. The SUM total for 45 would equal 2 occasions etc. Therefore the sum 21 would equal 1 and go in the same row as ... Cells(RowCount, "B").Value = i ... where the value 21 is. It is a count (the same as the existing program) of the number of times each SUM has occured and will go in column "H" on the same row as the corresponding number (i). If it has not occured then a zero will be entered. Thanks in Advance. All the Best. Paul On Oct 17, 11:39 am, Joel wrote: 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:D" & (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 "Paul Black" wrote: 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:D" & (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 On Oct 11, 1:10 pm, Joel wrote: 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 "PaulBlack" wrote: Hi Joel, One FINAL question, I promise. If I wanted to physically input values into cells for example, would ... With Range("B4") ' Setup Distribution Categories .Offset(0, 0).Value = "111111" .Offset(1, 0).Value = "211110" .Offset(2, 0).Value = "221100" .Offset(3, 0).Value = "222000" .Offset(4, 0).Value = "311100" .Offset(5, 0).Value = "321000" .Offset(6, 0).Value = "330000" .Offset(7, 0).Value = "411000" .Offset(8, 0).Value = "420000" .Offset(9, 0).Value = "510000" .Offset(10, 0).Value = "600000" ' Format Distribution Categories .Resize(11, 1).HorizontalAlignment = xlLeft End With .... be the right way to do it. Thanks in Advance. All the Best. Paul On Oct 11, 12:21 wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "PaulBlack" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain ... read more »- Hide quoted text - - Show quoted text - |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gap between Headings and Start of Output
Sorry Joel,
There will only be 6 number combinations, so the columns used will always be "B:G". It is the number of rows that will change. There could be 3,000 or so rows of draws to evaluate. As you know though the SUM totals can only be from 21 to 279. Thanks in Advance. All the Best. Paul On Oct 17, 11:56 am, Paul Black wrote: Thanks for the reply Joel, I think I confused things with my explanation. If I have the combinations ... 1 2 3 4 5 6 = 21 1 2 3 5 6 7 = 24 1 2 3 6 7 8 = 27 1 2 3 7 8 9 = 30 1 2 3 8 9 10 = 33 1 2 3 9 10 11 = 36 1 2 3 10 11 12 = 39 1 2 3 11 12 16 = 45 1 2 3 12 13 14 = 45 ... for example, the SUM total for 21 would equal 1 occasion. The SUM total for 45 would equal 2 occasions etc. Therefore the sum 21 would equal 1 and go in the same row as ... Cells(RowCount, "B").Value = i ... where the value 21 is. It is a count (the same as the existing program) of the number of times each SUM has occured and will go in column "H" on the same row as the corresponding number (i). If it has not occured then a zero will be entered. Thanks in Advance. All the Best.Paul On Oct 17, 11:39 am, Joel wrote: 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:D" & (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 "PaulBlack" wrote: 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:D" & (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 On Oct 11, 1:10 pm, Joel wrote: 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 "PaulBlack" wrote: Hi Joel, One FINAL question, I promise. If I wanted to physically input values into cells for example, would ... With Range("B4") ' Setup Distribution Categories .Offset(0, 0).Value = "111111" .Offset(1, 0).Value = "211110" .Offset(2, 0).Value = "221100" .Offset(3, 0).Value = "222000" .Offset(4, 0).Value = "311100" .Offset(5, 0).Value = "321000" .Offset(6, 0).Value = "330000" .Offset(7, 0).Value = "411000" .Offset(8, 0).Value = "420000" .Offset(9, 0).Value = "510000" .Offset(10, 0).Value = "600000" ' Format Distribution Categories .Resize(11, 1).HorizontalAlignment = xlLeft End With .... be the right way to do it. Thanks in Advance. All the Best. Paul On Oct 11, 12:21 wrote: Thanks Joel, The program now at works perfectly, thank you, but why?. Thanks in Advance. All the Best. Paul On Oct 11, 12:06 pm, Joel wrote: change your dim to the line below and add redim. Dim DistSum() As Single ReDim DistSum((6 * MaxBall) - 15) "PaulBlack" wrote: Thanks for the re-written code Joel, it is appreciated. One thing though, if I change the parameters ... Const MinDist As Integer = 21 Const MaxDist As Integer = 279 Dim DistSum(279) As Single .... to ... Const MinDist As Integer = 50 Const MaxDist As Integer = 250 Dim DistSum(250) As Single .... I get an error 9, subscript out of range on line ... DistSum(A + B + C + D + E + F) = _ DistSum(A + B + C + D + E + F) + 1 This program will be used for about 25 other calculations and ONLY the bit in between the For .. Next loop will change. The layout will be EXACTLY the same for all of them, so I appreciate I will not have to change much each time thanks to you. Thanks in Advance. All the Best. Paul On Oct 11, 1:18 am, Joel wrote: I re-wrote the code to make it easier to maintain ... read more »- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet has numeric column headings. Change to alpha headings? | Excel Discussion (Misc queries) | |||
Column headings to numbers and row headings to alphabets? | Excel Discussion (Misc queries) | |||
Set of varibles produces one output. Need series of output. | Excel Programming | |||
Trying to start a second line ( ie use of enter key) in an output file | Excel Programming | |||
Getting output from an excel output back to cscript.. | Excel Programming |