Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) | |||
Replacing formula | Excel Programming | |||
Replacing Formula | Excel Programming | |||
replacing value by formula | Excel Programming | |||
Replacing a value from a formula | Excel Programming |