Replacing subtotal with a formula
 

Hi all,

ref: http://www.geocities.com/tompel79/aging_test_before.xls
http://www.geocities.com/tompel79/aging_test_after.xls

I'm new at this. I would like to calculate the subtotal of an aging
report, generated from AccPac. Currently, the system will generate the
subtotal, but I would like to replace all the subtotal with a formula
(Refer to aging_test_before.xls). Is there any easy way to do this?
Thank you very much.

Ferri.

Replacing subtotal with a formula
 

They look awfully alike to me. What is the difference? The subtotal is a
formula, so what exactly are you wanting?

--

HTH

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


"iltheas" wrote in message
oups.com...
Hi all,

ref: http://www.geocities.com/tompel79/aging_test_before.xls
http://www.geocities.com/tompel79/aging_test_after.xls

I'm new at this. I would like to calculate the subtotal of an aging
report, generated from AccPac. Currently, the system will generate the
subtotal, but I would like to replace all the subtotal with a formula
(Refer to aging_test_before.xls). Is there any easy way to do this?
Thank you very much.

Ferri.

Replacing subtotal with a formula
 

Yup, in this case, the result is the same, but if the subtotal is not a
formula, if I were to edit any of the figure, I would have to
recalculate the subtotal. The example is only showing 4 records, while
the actual file may show thousands of records.

Replacing subtotal with a formula
 

I am not clear what it is to be subtotalled. If it is the values in I,J,K &
L over customer number, then you could try this in the K subtotal

=SUM(INDIRECT("K"&MAX((A1:A4<"")*ROW(A1:A4))&":K" &ROW()-1))#

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

Adjust the others for column.

--

HTH

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


"iltheas" wrote in message
oups.com...
Yup, in this case, the result is the same, but if the subtotal is not a
formula, if I were to edit any of the figure, I would have to
recalculate the subtotal. The example is only showing 4 records, while
the actual file may show thousands of records.

Replacing subtotal with a formula
 

I tested this OK.
The rest is easy.

=====
Dim i As Integer
Dim srow As Integer
Dim erow As Integer

Sub main()
For i = 15 To Range("B1000").End(xlUp).Row
startrow
endrow
Next i
End Sub

Sub startrow()
Do Until Range("B" & i) = "IN"
i = i + 1
Loop
srow = i
End Sub

Sub endrow()
Do Until Range("B" & i) < "IN"
i = i + 1
Loop
erow = i - 1
End Sub


"iltheas" wrote:

Yup, in this case, the result is the same, but if the subtotal is not a
formula, if I were to edit any of the figure, I would have to
recalculate the subtotal. The example is only showing 4 records, while
the actual file may show thousands of records.

Replacing subtotal with a formula
 

Hi Bob,
Fyi, this is not for myself, it's for my supervisor. I suspect he's
just being lazy, but since I'm also interested in learning vba (and it
doesn't hurt to impress him :P), so I agreed to try it out. As I said,
basically, when the system generate this list, he tends to edit the
figure, maybe put remarks here and there, and instead of subtotalling
each account everytime he edits one of the figure, he wants it to be
done automatically. Anyway, I'm not sure how to apply your formula,
it's way too complicated for me. It'll take a while for me to
understand it. In the mean time, I will try PY & Associates' solution.

Hi PY & Associates,
Thanks a lot, your codes would definitely help me start :)

Replacing subtotal with a formula
 

Okay, that is understandable, but you should try my formula.

This formula should go in I19, copy across to L19, then copy I19:L19 to
I23:L23, I27:L27 etc..

=SUM(INDIRECT(CHAR(COLUMN()+64)&MAX((A1:A18<"")*R OW(A1:A18))&":"&CHAR(COLUM
N()+64)&ROW()-1))

remember to Ctrl-Shift-Enter

--

HTH

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


"iltheas" wrote in message
ups.com...
Hi Bob,
Fyi, this is not for myself, it's for my supervisor. I suspect he's
just being lazy, but since I'm also interested in learning vba (and it
doesn't hurt to impress him :P), so I agreed to try it out. As I said,
basically, when the system generate this list, he tends to edit the
figure, maybe put remarks here and there, and instead of subtotalling
each account everytime he edits one of the figure, he wants it to be
done automatically. Anyway, I'm not sure how to apply your formula,
it's way too complicated for me. It'll take a while for me to
understand it. In the mean time, I will try PY & Associates' solution.

Hi PY & Associates,
Thanks a lot, your codes would definitely help me start :)

Replacing subtotal with a formula
 

Have you solved your problem please?

Regards
PY & Associates
Excel customization service provider


"iltheas" wrote in message
ups.com...
Hi Bob,
Fyi, this is not for myself, it's for my supervisor. I suspect he's
just being lazy, but since I'm also interested in learning vba (and it
doesn't hurt to impress him :P), so I agreed to try it out. As I said,
basically, when the system generate this list, he tends to edit the
figure, maybe put remarks here and there, and instead of subtotalling
each account everytime he edits one of the figure, he wants it to be
done automatically. Anyway, I'm not sure how to apply your formula,
it's way too complicated for me. It'll take a while for me to
understand it. In the mean time, I will try PY & Associates' solution.

Hi PY & Associates,
Thanks a lot, your codes would definitely help me start :)