Another Newbee ?. Automatic Count and Sum

[mbarnett]

Hi All,

I wrote a macro to read in inforamtion based on a certain criteria. The macro reads in the info and prints the info in a seperate sheet fine, but I can not figure out how to place a count or a sum on two data points. For example-

Criteria Age Amt
A 48 100
A 52 200
B 60 500
B 38 200

Is there a way to have the code automatically count how many ages right after criteria A and add the total after criteria A and then do the same for criteria B. The data is always changing and I don't want to put in sums on the sheet because the ranges can vary. Anyone have any ideas. I inserted the code- your critique would be appreciated. thanks Again.


Sub read_data(sec() As Nostro)
'read in info from nostro detail sheet
Dim i As Integer, num_record As Integer

Sheets("Nostro Detail&quot.Select
i = 2
num_record = 0
Do While (Cells(i, 1).Value <> &quot;&quot
If find_sec(Cells(i, 22).Value) Then
num_record = num_record + 1
ReDim Preserve sec(1 To num_record)
sec(num_record).critera = Cells(i, 22).Value
sec(num_record).age = Cells(i, 3).Value
sec(num_record).ccy = Cells(i, 11).Value
sec(num_record).ccy__amount = Cells(i, 10).Value
sec(num_record).new_spn = Cells(i, 5).Value
sec(num_record).nm_type = Cells(i, 13).Value
sec(num_record).sett_type = Cells(i, 16).Value
sec(num_record).vdate = Cells(i, 9).Value
Else
End If
i = i + 1
Loop
End Sub
Sub print_results(sec() As Nostro)

Dim i As Integer

Sheets(&quot;Sheet1&quot.Select

For i = 1 To UBound(sec)
Cells(i + 1, 1).Value = sec(i).critera
Cells(i + 1, 2).Value = sec(i).age
Cells(i + 1, 3).Value = sec(i).amt

Next i
End Sub

 

[SkipVought]

Hi,

Try using the Subtotal Wizard. You can count, sum, average on breaks such as your Criteria. No coding -- just the Subtotal Wizard.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbarnett]

Hi Skip,

Thanks for your reply. I thought about using subtotal wizard, but it won't allow two criterias one for sum in amt field and count for age. I really want to automate the whole process and want to incorporate it in my code. Any other suggestion.

 

[SkipVought]

Hi,

This combines the Subtotal Wizard doing counts and some code to do sums. See if this meets your needs...

Sub IncludeSums()
    Dim SumFormula As String
    RemoveSubtotals
    InsertSubtotals
    For Each r In Range(Cells(2, 2), Cells(2, 2).End(xlDown))
        If Left(r.Formula, 9) = "=SUBTOTAL" Then
            r.Copy Destination:=r.Offset(0, 1)
            SumFormula = r.Offset(0, 1).Formula
            r.Offset(0, 1).Formula = Left(SumFormula, 10) & "9" & _
                    Right(SumFormula, Len(SumFormula) - 11)
        End If
    Next
End Sub
Sub InsertSubtotals()
    Cells(1, 1).CurrentRegion.Subtotal _
        GroupBy:=1, _
        Function:=xlCount, _
        TotalList:=Array(2), _
        Replace:=True, _
        PageBreaks:=False, _
        SummaryBelowData:=True
End Sub
Sub RemoveSubtotals()
    Cells(1, 1).RemoveSubtotal
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com