Sum on Dynamic Data Range - Macro

I am writing a VBA macro and have come across something I cannot figure
out. I have a spreadsheet and in column B is the product ID field.
There are 3 different product ID's and each of the products in column A
are given an ID based on a VLookup. At each change in the product ID
in column B, the macro adds a row to the spreadsheet and names that row
based on the product ID. Column C has the amount of sales for each
product category....and I want to have a total of all sales by product
ID in the new row that is added. Here is an example

Writing Tools (Prod ID) Sales
Pens 1.00
Pencils 2.00
Highliters 3.00
6.00 (This is where I need a
dynamic Sum!)
Paper (Prod ID)
Notebooks
Loose Leaf
Printer
(Another Dynamic Sum is needed
here....)
Misc (Prod ID)
Paper Clips
Tacks
Etc.........

The problem I am having is each time this file is saved the number of
products for each of the 3 product ID's may or may not change. How can
I tell Excel to only add the specific # of cells for that specific
category each time the macro is run?? Hope this makes some sort of
sense! Any help is MUCH appreciated!!

-Chris

Why not use either subtotals or a pivot table - both can be made dynamic

--
Cheers
Nigel



<[email protected]> wrote in message
news:[email protected]...
> I am writing a VBA macro and have come across something I cannot figure
> out. I have a spreadsheet and in column B is the product ID field.
> There are 3 different product ID's and each of the products in column A
> are given an ID based on a VLookup. At each change in the product ID
> in column B, the macro adds a row to the spreadsheet and names that row
> based on the product ID. Column C has the amount of sales for each
> product category....and I want to have a total of all sales by product
> ID in the new row that is added. Here is an example
>
> Writing Tools (Prod ID) Sales
> Pens 1.00
> Pencils 2.00
> Highliters 3.00
> 6.00 (This is where I need a
> dynamic Sum!)
> Paper (Prod ID)
> Notebooks
> Loose Leaf
> Printer
> (Another Dynamic Sum is needed
> here....)
> Misc (Prod ID)
> Paper Clips
> Tacks
> Etc.........
>
> The problem I am having is each time this file is saved the number of
> products for each of the 3 product ID's may or may not change. How can
> I tell Excel to only add the specific # of cells for that specific
> category each time the macro is run?? Hope this makes some sort of
> sense! Any help is MUCH appreciated!!
>
> -Chris
>