MS Excel macro question: relative formulas

[John Meredith]

I'm writing a macro in MS Excel which should do the following:

(1) Scan the first column, which contains various text fields.
(2) When there is a variation, insert a blank line, and calculate
a subtotal for the group.
(3) Move onto the next group, and continue checking.

That isn't really clear, so here's a brief diagram:
(This isn't really the data I'm working with)

DEPT. EMPLOYEE # TYPE 1 JOBS TYPE 2 JOBS TYPE 3 JOBS
=================================================================
ACCTG 8776 12 26 3
ACCTG 3774 12 3 12
ACCTG 8764 33 23 12
ACCTG 1244 4 54 33
PR 7096 22 8 11
PR 5678 7 22 17
ADVERT 4894 34 4 21
ADVERT 4323 4 6 19
ADVERT 3422 33 24 15
ADVERT 7878 5 34 32

(In reality, I'm dealing with 12 worksheets of over 1000 lines
each and 20 columns to sum, so you can see why the macro is a
necessity).

For each of the departments, I need to insert blank line
and then go across that row and sum each field. The
problem I'm having is making the macro compose a formula
and paste it into the worksheet, which I need because we
will be performing some "what-if" analysis on the data.

Any help greatly appreciated. If you have ideas and need
more info, please send e-mail or post.

John P. Meredith Management Information Systems
Penn State University Smeal College of Business Administration
<JPM...@psuvm.psu.edu> (814) 861-7037 [h] (814) 863-1919 [o]

[Michael Alan Roberts]

In article <93141.091...@psuvm.psu.edu> John Meredith <JPM...@psuvm.psu.edu> writes:
>I'm writing a macro in MS Excel which should do the following:
>
>(1) Scan the first column, which contains various text fields.
>(2) When there is a variation, insert a blank line, and calculate
> a subtotal for the group.
>(3) Move onto the next group, and continue checking.
>
>That isn't really clear, so here's a brief diagram:
> (This isn't really the data I'm working with)
>
>DEPT. EMPLOYEE # TYPE 1 JOBS TYPE 2 JOBS TYPE 3 JOBS
>=================================================================
>ACCTG 8776 12 26 3
>ACCTG 3774 12 3 12
>ACCTG 8764 33 23 12
>ACCTG 1244 4 54 33
>PR 7096 22 8 11
>PR 5678 7 22 17
>ADVERT 4894 34 4 21
>ADVERT 4323 4 6 19
>ADVERT 3422 33 24 15
>ADVERT 7878 5 34 32
>

>For each of the departments, I need to insert blank line
>and then go across that row and sum each field. The
>problem I'm having is making the macro compose a formula
>and paste it into the worksheet, which I need because we
>will be performing some "what-if" analysis on the data.
>

Try the macro function:

=FORMULA("=SUM(whatever)")

...to paste a formula into a worksheet from a macro.

Hope this helps.

Michael Roberts
rob...@ucunix.san.uc.edu