Formula in macro

[sujittalukde]

I am running a macro which calculated subtotal and total but the GRAND TOTAL is taking the subtotal figure also. So I wnat to set the GRAND TOTAL formula in the macro itself so that GRAND TOTAL comes corrctly?

A picture is attached to clarify the matter.

Book8
	A	B	C	D
1	A	2
2	B	3
3	C	4
4	AA	5
5	D	6
6	E	7
7	F	8
8	AA	9
9	G	10
10	H	11
11	I	12
12	AA	13
13	GRAND TOTAL	63	=((SUM(B1:B12))-(SUMIF(A1:B12,"aa",B1:B12)))
14		This calculation has to be made in macro
Sheet1

How this can be done?

 

[njimack]

Don't use SUM or SUMIF - use SUBTOTAL...

=SUBTOTAL(9,B1:B12)

 

[sujittalukde]

THe macro I have use the sum function. I have made some modification to it so the GRAND TOTAL is changing So I need to change the formula of Grand Total

 

[ravishankar]

Hi
Try
Grand total =(SUM(B1:B12)-SUMIF(A1:A12,"aa",B1:B12))
ravi

 

[sujittalukde]

This is what I am already shown in my first post. This i wnat to set in the macro. HOw can I do it?

 

[VoG]

Perhaps

Range("B13").Formula = "=SUM(B1:B12)-SUMIF(A1:A12,""aa"",B1:B12)"