Last Attempt on this Question- two conditions 1

[mbarnett]

Hi All,
I think this a great site and as a new user I would like to contibute finacially to the site- how can this be done? Know to my last attempt at this question -

I have a macro and it reads in data and prints it out. Once the macro prints the data. Is there a way (Ican not use Subtotals). Based on a criteria to run another macro to insert sums under one field and a count under anthoer field. I would do it manually but the data can vary from 10 sets of criteria to 100. For eample

Critera Age Amt Reason
A 25 100 Unknown
A 50 75 Unknown
B 10 15 New
B 45 30 Unknown
C 10 18 New


Is there a way once the info is printed in two run a macro that will insert a row after each criteria and count the Age field for that criteria and Sum the amount field for that criteria. Again your help would be greatly appreciated.

Mark

 

[SkipVought]

I think that I just answered this question in your original thread.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbarnett]

Hi Skip,

You Can't Apply two subtotals criteria. I tried. It did not work.

 

[SkipVought]

Did you use my code?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbarnett]

No- What code...I'm sorry It was not attached

 

[SkipVought]

This code...
Run IncludeSums...

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

 

[mbarnett]

SkipVought,

You are the man. It worked great. I just had to dim r as Variant and it workeed like a charm. I have to give you a star. Thanks Again for your help!!!

 

[SkipVought]

r is actually a Range

Thanx!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com