P
Paul Black
Hi Everybody,
I Have the Following Macro which Produces a List of 7 Totals.
Rather than Using …
ActiveCell.Offset(7, 0).Value = nType(1) + nType(2) + nType(3) +
nType(4) _
+ nType(5) + nType(6) + nType(7)
… to Calculate the Grand Total, is there an Easier VB Solution Rather
than to Use an Excel Formula in the Code Please.
The Macro I am Using is :-
Option Explicit
Option Base 1
Sub Produce_Totals()
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 I As Integer
Dim nType(7) As Double
Application.ScreenUpdating = False
Sheets("Totals").Select
Range("C4").Select
For I = 1 To 7
nType(I) = 0
Next I
For A = 1 To 25
For B = A + 1 To 26
For C = B + 1 To 27
For D = C + 1 To 28
For E = D + 1 To 29
For F = E + 1 To 30
If F - A >= 10 And F - A <= 12 Then nType(1) = nType(1) + 1
If F - A >= 13 And F - A <= 16 Then nType(2) = nType(2) + 1
If F - A >= 17 And F - A <= 19 Then nType(3) = nType(3) + 1
If F - A >= 20 And F - A <= 21 Then nType(4) = nType(4) + 1
If F - A >= 22 And F - A <= 24 Then nType(5) = nType(5) + 1
If F - A >= 25 And F - A <= 27 Then nType(6) = nType(6) + 1
If F - A >= 28 And F - A <= 30 Then nType(7) = nType(7) + 1
Next F
Next E
Next D
Next C
Next B
Next A
For I = 1 To 7
ActiveCell(I, 1).Value = nType(I)
' ActiveCell.Offset(7, 0).Value = nType(1) + nType(2) + nType(3) +
nType(4) _
+ nType(5) + nType(6) + nType(7)
Next I
Application.ScreenUpdating = True
End Sub
I have Tried Including Another Variable to Total them But without ANY
Success. I think I Might have Been Trying to Put it in the Wrong Place
or Something.
Any Help will be Appreciated.
Thanks in Advance
All the Best
Paul
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I Have the Following Macro which Produces a List of 7 Totals.
Rather than Using …
ActiveCell.Offset(7, 0).Value = nType(1) + nType(2) + nType(3) +
nType(4) _
+ nType(5) + nType(6) + nType(7)
… to Calculate the Grand Total, is there an Easier VB Solution Rather
than to Use an Excel Formula in the Code Please.
The Macro I am Using is :-
Option Explicit
Option Base 1
Sub Produce_Totals()
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 I As Integer
Dim nType(7) As Double
Application.ScreenUpdating = False
Sheets("Totals").Select
Range("C4").Select
For I = 1 To 7
nType(I) = 0
Next I
For A = 1 To 25
For B = A + 1 To 26
For C = B + 1 To 27
For D = C + 1 To 28
For E = D + 1 To 29
For F = E + 1 To 30
If F - A >= 10 And F - A <= 12 Then nType(1) = nType(1) + 1
If F - A >= 13 And F - A <= 16 Then nType(2) = nType(2) + 1
If F - A >= 17 And F - A <= 19 Then nType(3) = nType(3) + 1
If F - A >= 20 And F - A <= 21 Then nType(4) = nType(4) + 1
If F - A >= 22 And F - A <= 24 Then nType(5) = nType(5) + 1
If F - A >= 25 And F - A <= 27 Then nType(6) = nType(6) + 1
If F - A >= 28 And F - A <= 30 Then nType(7) = nType(7) + 1
Next F
Next E
Next D
Next C
Next B
Next A
For I = 1 To 7
ActiveCell(I, 1).Value = nType(I)
' ActiveCell.Offset(7, 0).Value = nType(1) + nType(2) + nType(3) +
nType(4) _
+ nType(5) + nType(6) + nType(7)
Next I
Application.ScreenUpdating = True
End Sub
I have Tried Including Another Variable to Total them But without ANY
Success. I think I Might have Been Trying to Put it in the Wrong Place
or Something.
Any Help will be Appreciated.
Thanks in Advance
All the Best
Paul
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!