Subtotal calculation using Excel Macro

Hello,

I have been trying to create an excel macro that should do a subtotal of some selected fields in my worksheet. The worksheet table looks something like:

CLIENT ORDER NO SALES H1 SALES H2
abcd 1234 100 300
abcd 1235 200 400
abcd 1240 500 100
mnop 1212 600 700
wxyz 1300 100 200
wxyz 1315 300 400
wxyz 1325 500 600
wxyz 1335 700 800

What the macro should do is to do a subtotal of the sales in H1 and H2 against each of these clients. That is, the output of the macro should be like:

CLIENT SALES H1 SALES H2
abcd 800 800
mnop 600 700
wxyz 1600 2000

I know how to do it manually using the SUBTOTAL function, but I need to implement this feature in a macro, which I am not able to do. Could anyone kindly provide me with some assistance please.

Cheers.

Hi sntnknd and welcome to the forum! :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

I know how to do it manually using the SUBTOTAL function, but I need to implement this feature in a macro, which I am not able to do. Could anyone kindly provide me with some assistance please.

You have lots of non-VBA options here including using a pivottable, SUMPRODUCT or array formulas.

Can you give us an idea of the bigger picture so we can understand why you feel that a VBA solution is necessary?

If you do decide to go the "VBA route", do you want the result cells to contain formulas or just numbers?

Colin

Hello Colin,

The reason to have a macro here is due to the fact that my master worksheet table can have dynamic values, which are populated by some macro. As such, the number of rows is also dynamic.

I would like to design it such that once a particular form button is clicked, this new macro should run on this worksheet table, perform the necessary conditional subtotal and populate a 2nd table.

I thought it would be easier to implement this via a macro. But maybe if there is any better way of doing this, could you kindly suggest me please.

Please let me know if you need more details. Thank you for your kind response.

cheers.

A really useful feature in Excel is dynamic named ranges.

Here's an example:

Select the sheet that contains your data (I'll assume the sheet is called "data" for my example).
Go to Insert --> Name --> Define
Type in CLIENT in the Names in workbook field.
In the refers to field type in this formula:

=OFFSET(data!$A$1,0,0,COUNTA(data!$A:$A),1)

Click on Add and OK.

The named range "CLIENT" will now refer to the range A1:A* where * is the last row (it checks for numbers and text).
You can then use this named range in formulas or pivottables. You can also either set up additional dynamic named ranges for your other fields, or you can change the width parameter of the OFFSET function to make your named range refer to more columns. ;)