Error in computed Subtotal in Range

[airforceone]

hi again,
I'm using set of VBA but does not compute the range properly! what seems to be the problem? i shall implement it in a loop sequence but the second computation is a bit off

    Range("A2:M" & LastRow).AutoFilter 1, "=" & "*DAMAGE TO PROPERTY"
    Range("B10") = WorksheetFunction.Subtotal(9, Range("B2:B14"))
    Range("C10") = WorksheetFunction.Subtotal(9, Range("C2:C14"))
    Range("D10") = WorksheetFunction.Subtotal(9, Range("D2:D14"))
    Range("B1").AutoFilter 'Turn autofilter Off

XXXXXXXXXXXXXX UCPER DATA.xls
	A	B	C	D
1	PARTICULAR	RURAL	URBAN	CITY
2	PORCH		8
3	VERANDA		8
4	ROOF		1
5	DAMAGE TO PROPERTY	6	3	1
6	MULTIPLE DAMAGE TO PROPERTY	0	2
7	REPAIRED	3	2
8	SOLD	1	1
TEST_MERGE (7)

 

[jolivanes]

Re: "second computation is a bit off"
How much?
Don't you get a total of 10?

 

[Alex Blakenburg]

Your output row is Row 10 which is in the middle of the Filter range of row 2:14, that is never going to work.
Also starting the Range at row 2 was not making row 1 the filter row.

Maybe:

    Range("A1:M" & LastRow).AutoFilter Field:=1, Criteria1:= _
    "=*DAMAGE TO PROPERTY*"

    Range("B" & LastRow + 2) = WorksheetFunction.Subtotal(9, Range("B2:B" & LastRow))
    Range("C" & LastRow + 2) = WorksheetFunction.Subtotal(9, Range("C2:C" & LastRow))
    Range("D" & LastRow + 2) = WorksheetFunction.Subtotal(9, Range("D2:D" & LastRow))
    Range("B1").AutoFilter 'Turn autofilter Off

 

[airforceone]

Re: "second computation is a bit off"
How much?
Don't you get a total of 10?

yes mate 14...

 

[airforceone]

Your output row is Row 10 which is in the middle of the Filter range of row 2:14, that is never going to work.
Also starting the Range at row 2 was not making row 1 the filter row.

Maybe:

    Range("A1:M" & LastRow).AutoFilter Field:=1, Criteria1:= _
    "=*DAMAGE TO PROPERTY*"

    Range("B" & LastRow + 2) = WorksheetFunction.Subtotal(9, Range("B2:B" & LastRow))
    Range("C" & LastRow + 2) = WorksheetFunction.Subtotal(9, Range("C2:C" & LastRow))
    Range("D" & LastRow + 2) = WorksheetFunction.Subtotal(9, Range("D2:D" & LastRow))
    Range("B1").AutoFilter 'Turn autofilter Off

thanks for the info mate....
just notice I accidentally started at A2! well I should start at A1
All is good now, but thanks to all...

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.