Macro Loop, Find, and Total

[Lou]

We have a worksheet that can have a varying number of rows.

A B C D E F G H I

1 vvv vvv vvv 23
2 a vvv vvv 34
3 b vvv vvv 34
4 vvv vvv vvv 55
5 a vvv vvv 11
..
..
53 Sub-total 123 444 456 676 333 323
54
55 Gr ttl 2,355

The "vvv's" are variable alphanumberic entries.

We are trying to write a macro that will loop through the
worksheet and pick up a running total of the numbers in
columns D though I for those rows that have either an "a"
or a "b" in column A.

We want to insert a new row under the "Sub-total" row,
name it "CASCA", and enter the running totals by column as
a negative number.
Then we would insert a new "Total" row after that and
enter the sum of the sub-total row and the CASCA row.

A B C D E F G H I

1 vvv vvv vvv 23
2 a vvv vvv 34
3 b vvv vvv 34
4 vvv vvv vvv 55
5 a vvv vvv 11
..
..
53 Sub-total 123 444 456 676 333 323
54 CASCA (68) (11)
55 Total 123 376 456 676 322 323
56
57 Gr ttl 2,276

 

[Frank Kabel]

Hi
doesit has to be a macro?. As SUMPRODUCT should handle
(most) of your requirements.
e.g.
=SUMPRODUCT((A1:A100="a")*(D1:I100))

 

[Lou]

Frank, I think that it does need to be a macro. We have a
rather large macro that imports a text file, sorts data
into various worksheeets and performs some other routines
for the users.

This would be a module that is a part of the larger macro
and we are trying to accomplish the activity described
below. The number of rows can be 10 or 600 and it seems to
me that we need a macro to be able to accomplish the
outcomes we want. The large macro runs so that the end
user only has to enter "ctrl-r" and everything happens
automatically.