Summing different # of rows
 

How do I write a macro that will enter the sum in the last row, regardless
of how many rows I have listed.

For example:
Will calculate ttl he but also he
Item Qty Item
Qty
A 1 F
7
B 2 G
8
C 3 H
9
Total: 6 I
10

J 11

Total: 45

Thank you very much for your help and expertise!

Summing different # of rows
 

Let me try rewriting that quest so it is more legible.
I would like to enter the sum of all previous rows when the number of rows
changes from worksheet to worksheet.
For example:
Worksheet1
Item Qty
A 1
B 2
C 3
Ttl: 6

In Worksheet2
Item Qty
A 1
B 2
C 3
D 4
E 5
Ttl: 15

Thank you for your help

Summing different # of rows
 

Length = ActiveSheet.UsedRange.Rows.Count
sumtotal = WorksheetFunction.Sum(Range(Cells(1, 1), Cells(Length, 1)))
Cells(Length + 1, 1).Value = sumtotal


And in my macro there, change it so that wherever it says Cells(#,#)
the second number should be the number of the column that the data to
be summed is in.

Summing different # of rows
 

Length = ActiveSheet.UsedRange.Rows.Count
sumtotal = WorksheetFunction.Sum(Range(Cells(1, 1), Cells(Length, 1)))
Cells(Length + 1, 1).Value = sumtotal


And in my macro there, change it so that wherever it says Cells(#,#)
the second number should be the number of the column that the data to
be summed is in.

Summing different # of rows
 

Try this

=SUM(OFFSET(B2,,,COUNTA(A:A)-2))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"new2macros" wrote in message
...
Let me try rewriting that quest so it is more legible.
I would like to enter the sum of all previous rows when the number of rows
changes from worksheet to worksheet.
For example:
Worksheet1
Item Qty
A 1
B 2
C 3
Ttl: 6

In Worksheet2
Item Qty
A 1
B 2
C 3
D 4
E 5
Ttl: 15

Thank you for your help

Summing different # of rows
 

something like

=AVERAGE(IF(B2:B102,B2:B10))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"new2macros" wrote in message
...
How do I write a macro that will enter the sum in the last row,
regardless
of how many rows I have listed.

For example:
Will calculate ttl he but also he
Item Qty Item
Qty
A 1 F
7
B 2
G
8
C 3
H
9
Total: 6 I
10

J 11

Total: 45

Thank you very much for your help and expertise!

Summing different # of rows
 

sorry, response to another question.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" wrote in message
...
something like

=AVERAGE(IF(B2:B102,B2:B10))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"new2macros" wrote in message
...
How do I write a macro that will enter the sum in the last row,
regardless
of how many rows I have listed.

For example:
Will calculate ttl he but also he
Item Qty Item
Qty
A 1
F
7
B 2
G
8
C 3
H
9
Total: 6
I
10

J 11

Total: 45

Thank you very much for your help and expertise!