Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Grand Total and Positioning Please
Hi Everyone,
I have a Macro that Calculates the Total Results for a Width of a Line and Writes them to a Spreadsheet, this Works Perfectly. I Need Two More things though Please. The First Being the Grand Total of the Results Directly Underneath the Last Results Value. The Second is for it to Actually Output the Results Starting in Cell "A1", I have the Range Set to "A1" in the Macro, But for Some Reason it Starts the Results Output in Cell "A2". Any Help will be Greatly Appreciated. This is the Macro I am Using :- Option Explicit Option Base 1 Sub Spread() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim nType(7) As Double Dim I As Integer Application.ScreenUpdating = False Sheets("Output").Select Range("A1").Select For I = 1 To 7 nType(I) = 0 Next I For A = 1 To 24 For B = A + 1 To 25 For C = B + 1 To 26 For D = C + 1 To 27 For E = D + 1 To 28 For F = E + 1 To 29 If F - A = 5 And F - A <= 7 Then nType(1) = nType(1) + 1 If F - A = 8 And F - A <= 10 Then nType(2) = nType(2) + 1 If F - A = 11 And F - A <= 13 Then nType(3) = nType(3) + 1 If F - A = 14 And F - A <= 16 Then nType(4) = nType(4) + 1 If F - A = 17 And F - A <= 19 Then nType(5) = nType(5) + 1 If F - A = 20 And F - A <= 24 Then nType(6) = nType(6) + 1 If F - A = 25 And F - A <= 29 Then nType(7) = nType(7) + 1 Next F Next E Next D Next C Next B Next A For I = 1 To 7 ActiveCell.Offset(I, 0).Value = nType(I) Next I End Sub Thanks in Advance. All the Best Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Grand Total and Positioning Please
Option Explicit
Option Base 1 Sub Spread() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim nType(7) As Double Dim I As Integer Application.ScreenUpdating = False Sheets("Output").Select Range("A1").Select For I = 1 To 7 nType(I) = 0 Next I For A = 1 To 24 For B = A + 1 To 25 For C = B + 1 To 26 For D = C + 1 To 27 For E = D + 1 To 28 For F = E + 1 To 29 If F - A = 5 And F - A <= 7 Then nType(1) = nType(1) + 1 If F - A = 8 And F - A <= 10 Then nType(2) = nType(2) + 1 If F - A = 11 And F - A <= 13 Then nType(3) = nType(3) + 1 If F - A = 14 And F - A <= 16 Then nType(4) = nType(4) + 1 If F - A = 17 And F - A <= 19 Then nType(5) = nType(5) + 1 If F - A = 20 And F - A <= 24 Then nType(6) = nType(6) + 1 If F - A = 25 And F - A <= 29 Then nType(7) = nType(7) + 1 Next F Next E Next D Next C Next B Next A For I = 1 To 7 Cells(I, 1).Value = nType(I) Next I Range("A8").Formula = "=Sum(A1:A7)" End Sub -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi Everyone, I have a Macro that Calculates the Total Results for a Width of a Line and Writes them to a Spreadsheet, this Works Perfectly. I Need Two More things though Please. The First Being the Grand Total of the Results Directly Underneath the Last Results Value. The Second is for it to Actually Output the Results Starting in Cell "A1", I have the Range Set to "A1" in the Macro, But for Some Reason it Starts the Results Output in Cell "A2". Any Help will be Greatly Appreciated. This is the Macro I am Using :- Option Explicit Option Base 1 Sub Spread() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim F As Integer Dim nType(7) As Double Dim I As Integer Application.ScreenUpdating = False Sheets("Output").Select Range("A1").Select For I = 1 To 7 nType(I) = 0 Next I For A = 1 To 24 For B = A + 1 To 25 For C = B + 1 To 26 For D = C + 1 To 27 For E = D + 1 To 28 For F = E + 1 To 29 If F - A = 5 And F - A <= 7 Then nType(1) = nType(1) + 1 If F - A = 8 And F - A <= 10 Then nType(2) = nType(2) + 1 If F - A = 11 And F - A <= 13 Then nType(3) = nType(3) + 1 If F - A = 14 And F - A <= 16 Then nType(4) = nType(4) + 1 If F - A = 17 And F - A <= 19 Then nType(5) = nType(5) + 1 If F - A = 20 And F - A <= 24 Then nType(6) = nType(6) + 1 If F - A = 25 And F - A <= 29 Then nType(7) = nType(7) + 1 Next F Next E Next D Next C Next B Next A For I = 1 To 7 ActiveCell.Offset(I, 0).Value = nType(I) Next I End Sub Thanks in Advance. All the Best Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Grand Total and Positioning Please
Thanks Tom,
It Works Brilliant. Out of Interest, is it Good Practice to Put Application.ScreenUpdating = True Before the End Sub. Does it Cause the Macro Any Problems NOT Including it. All the Best Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Grand Total and Positioning Please
It doesn't hurt either way.
-- Regards, Tom Ogilvy "Paul Black" wrote in message ... Thanks Tom, It Works Brilliant. Out of Interest, is it Good Practice to Put Application.ScreenUpdating = True Before the End Sub. Does it Cause the Macro Any Problems NOT Including it. All the Best Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Grand Total and Positioning Please
Thanks Tom.
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
Pivots - Auto calc % Sub total is of grand total | Excel Discussion (Misc queries) | |||
Old Lotus Sub Total & Grand Total formula | Excel Discussion (Misc queries) | |||
Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions |