I have a sheet "Sales" which records the transactions.Row 1 is the
header.The records starts from row 2 downward.

Col A2 store date in "dd/mm/yyyy".
Col B2 store invoice number.
Col C2 store group.
Col D2 store machine.
Col E2 store brand
Col F2 store quantity.
Col G2 store unitprice.

I like to generate a report based on month and year against
group,machine & brand.

Currently I am using the array formula in another sheet "Report" to
extract the datas.

e.g. in "Report" col B2 the below array formula :

Range 2:659 is for the month of January,2004

=SUM(IF(Sales!$C$2:$C$659="Filter",IF(Sales!$D$2:$ D$659="Cat",IF(Sales!$E$2:$E$659="OEM"1,0))))

Currently there are altogether 10 types of machine and 20 types of
group but the brand is unfix.

Can someone help to do this in vba? I hope I have explain clearly.

Regards
Michael.


--
Michael168
------------------------------------------------------------------------
Michael168's Profile: http://www.excelforum.com/member.php...nfo&userid=605
View this thread: http://www.excelforum.com/showthread...hreadid=273579

It is already done for you. (no macro required) Use a Pivot table.
Data=Pivot Table Reports and Charts. Once created, you can select the date
field and group on Month and Year.

--
Regards,
Tom Ogilvy



"Michael168" wrote in message
...

I have a sheet "Sales" which records the transactions.Row 1 is the
header.The records starts from row 2 downward.

Col A2 store date in "dd/mm/yyyy".
Col B2 store invoice number.
Col C2 store group.
Col D2 store machine.
Col E2 store brand
Col F2 store quantity.
Col G2 store unitprice.

I like to generate a report based on month and year against
group,machine & brand.

Currently I am using the array formula in another sheet "Report" to
extract the datas.

e.g. in "Report" col B2 the below array formula :

Range 2:659 is for the month of January,2004


=SUM(IF(Sales!$C$2:$C$659="Filter",IF(Sales!$D$2:$ D$659="Cat",IF(Sales!$E$2:
$E$659="OEM"1,0))))

Currently there are altogether 10 types of machine and 20 types of
group but the brand is unfix.

Can someone help to do this in vba? I hope I have explain clearly.

Regards
Michael.


--
Michael168
------------------------------------------------------------------------
Michael168's Profile:
http://www.excelforum.com/member.php...nfo&userid=605
View this thread: http://www.excelforum.com/showthread...hreadid=273579