Products and subtotals

[LC]

Hi,

Let's say I have this:

A B cost C D E F G H I J...
1 Pets cats 19.00 1 23 2 4 23 45 43 23...
2 Pets cats 13.00 3 4 2 5 67 23 23 23...
..
..
total for cats...
3 Pets dogs 20.00 2 2 3 4 34 23 23 23...
4 Pets dogs 21.00 4 5 13 4 34 23 23 23...
total for dogs..
..
..
total for Pets..
....
....
....
The dots mean so on and so forth.
Can I some how take the C column value and multipe by its
respective cost for all the cats and then the same for D,
E columns, etc? Then have a total of the cost for all
the pets?

What I have is this: since I dont have a stable amount of
columns for pets...

*num is a variable for where the column of the array
should start..

Dim varr()
Dim rng As Range, i As Long
Set rng = Range("IV1").End(xlToLeft)
ReDim varr(1 To rng.Column - num)

For i = 1 To rng.Column - num
varr(i) = i + num
Next
ActiveCell.Range("A3").Select
Selection.subtotal GroupBy:=groups, Function:=xlSum, _
TotalList:=varr, Replace:=False, PageBreaks:=False, _
SummaryBelowData:=True


I would like to have a subtotal for the cost as well.

Thank you so much.

 

[Steve Smallman]

LC,

if you include a total column to the right of each line say column Z then in
cell Z1 enter the formula "=sum(d1:y1)*c1" you should get a total for that
line.

now, the total for cats line is relatively important, is that inserted
manually or are you using subtotals?

If subtotals, make sure you total column Z, hide the data columns (d:y)

and I think you have a solution.

We could get more complicated, but does this serve your purpose?

Steve

 

[LC]

Hi Steve,

Thanks for your help. Is there another way without
posting it in a total column b/c the person whom i'm
writting this VBA code for wants the cost*number from the
column for each column. And, that cost*number should
appear under each column.

For example:
Let's say I have this:

A B cost C D E F G H I J...
1 Pets cats 2.00 1 3 2 4 3 5 4 3...
2 Pets cats 3.00 3 4 2 5 7 3 2 3...
...
...total cost for all cats (please see below)
total for cats...
3 Pets dogs 2.00 2 2 3 4 34 23 23 23...
4 Pets dogs 1.00 4 5 13 4 34 23 23 23...
total for dogs..
...
...
total for Pets..
.....
.....
.....

**total cost for all cats:

... C D E F G H I J...
... $2 $6 $4 $8 $6 $10 $8 $6...
... $9 $12 $6 $15 $21 $9 $6 $9...
(then sum that up in another row
... $11 $18 $10 $23 $27 $19 $14 $15

If you could help me with that, that would be wonderful.

Thank you.