I need help to develop a looping macro formula to sum range of varying size of rows based on cell value ‘Total’ in column B. The sample data are downloaded with amount on Totals but not formula. The help I need is to develop formula on each row with 'Total' in column C then copy them to last column of the same row, however, need to loop on each row with ‘Total’. If you need sample file let me know.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Macro to loop based on cell value to sum varying size of rows
- Thread starter esSant593
- Start date
-
- Tags
- company: xyz
Excel Facts
Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
esSant593,
Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
Upvote
0
esSant593,
Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
See spreadsheet. The spreadsheets were downloaded and no formula on the Total row. The formula(Example: =sum(C38:c41) on C42 will be adding all amount in that column of the particular group. The macro will loop to all rows with Total in col. B. Once the formula has been set up on that row it will be copied to columns D-J.
| Excel Workbook | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Company: xyz | AFTER | ||||||||||
| 2 | A | B | C | D | E | F | G | H | I | J | ||
| 3 | 0080055539 | RA | (15.50) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | (15.50) | ||
| 4 | Total | (15.50) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | (15.50) | |||
| 5 | ||||||||||||
| 6 | 0080123887 | PY | (38,088.00) | 0.00 | 0.00 | (38,088.00) | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 7 | Total | (38,088.00) | 0.00 | 0.00 | (38,088.00) | 0.00 | 0.00 | 0.00 | 0.00 | |||
| 8 | ||||||||||||
| 9 | 0080072063 | IN | 56,448.00 | 0.00 | 56,448.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 10 | Total | 56,448.00 | 0.00 | 56,448.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
| 11 | ||||||||||||
| 12 | 0080055636 | AD | (277.00) | 0.00 | 0.00 | 0.00 | (15.00) | 0.00 | 0.00 | (262.00) | ||
| 13 | 0080055636 | IN | 4,032.00 | 4,032.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 14 | 0080055636 | PP | (41.50) | 2,340.00 | 0.00 | (2,340.00) | 0.00 | (41.50) | 0.00 | 0.00 | ||
| 15 | Total | 3,713.50 | 6,372.00 | 0.00 | (2,340.00) | (15.00) | (41.50) | 0.00 | (262.00) | |||
| 16 | ||||||||||||
| 17 | 0080055599 | AD | 7,264.00 | 0.00 | 0.00 | 0.00 | 7,264.00 | 0.00 | 0.00 | 0.00 | ||
| 18 | 0080055599 | IN | 447,433.50 | 320,047.20 | 85,038.60 | 42,347.70 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 19 | 0080055599 | PP | 54,422.00 | 0.00 | 0.00 | (13,140.00) | 67,562.00 | 0.00 | 0.00 | 0.00 | ||
| 20 | 0080055599 | RA | (267.50) | 0.00 | 0.00 | 0.00 | (252.00) | 0.00 | (15.50) | 0.00 | ||
| 21 | 0080055599 | SH | 1,976.50 | 0.00 | 0.00 | 0.00 | (399.50) | 0.00 | 2,376.00 | 0.00 | ||
| 22 | Total | 510,828.50 | 320,047.20 | 85,038.60 | 29,207.70 | 74,174.50 | 0.00 | 2,360.50 | 0.00 | |||
| 23 | ||||||||||||
| 24 | 0080055688 | AD | 56,986.07 | 0.00 | 0.00 | 20,545.41 | 36,440.66 | 0.00 | 0.00 | 0.00 | ||
| 25 | 0080055688 | IN | 4,558,844.79 | 3,360,248.59 | 744,555.90 | 454,040.30 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 26 | 0080055688 | PC | 355.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 355.00 | ||
| 27 | 0080055688 | PD | (72.00) | 0.00 | 0.00 | 0.00 | (72.00) | 0.00 | 0.00 | 0.00 | ||
| 28 | 0080055688 | PP | 76.50 | 0.00 | 0.00 | 76.50 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 29 | 0080055688 | RA | (11,484.18) | 0.00 | 0.00 | (1,461.22) | 10,076.94 | 0.00 | (240.00) | (19,859.90) | ||
| 30 | Total | 4,604,706.18 | 3,360,248.59 | 744,555.90 | 473,200.99 | 46,445.60 | 0.00 | (240.00) | (19,504.90) | |||
| 31 | ||||||||||||
| 32 | 0080055822 | RA | (258.50) | 0.00 | 0.00 | 0.00 | 0.00 | (252.00) | (6.50) | 0.00 | ||
| 33 | Total | (258.50) | 0.00 | 0.00 | 0.00 | 0.00 | (252.00) | (6.50) | 0.00 | |||
| 34 | ||||||||||||
| 35 | 0080055603 | IN | 500,889.60 | 500,889.60 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 36 | Total | 500,889.60 | 500,889.60 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
| 37 | ||||||||||||
| 38 | 0080110039 | IN | 18,144.00 | 0.00 | 0.00 | 18,144.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||
| 39 | 0080110039 | PD | 9,216.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 9,216.00 | 0.00 | ||
| 40 | 0080110039 | PP | (11,668.50) | 0.00 | 0.00 | 0.00 | 0.00 | (2,073.50) | 0.00 | (9,595.00) | ||
| 41 | 0080110039 | PY | (17,765.00) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | (16,014.00) | (1,751.00) | ||
| 42 | Total | (2,073.50) | 0.00 | 0.00 | 18,144.00 | 0.00 | (2,073.50) | (6,798.00) | (11,346.00) | |||
| 43 | ||||||||||||
| 44 | GrandTotal | 36,859,838.45 | 23,974,387.99 | 10,984,394.53 | 1,584,858.74 | 150,130.97 | 88,672.93 | 124,648.61 | (47,255.32) | |||
| 45 | ||||||||||||
| 46 | Looping macro to set up each ROW with Total with a formula. Once the formula has been set up it will be copied across to column D - J. | |||||||||||
DATA_US | ||||||||||||
Upvote
0
esSant593,
Sample data before the macro:
After the macro:
You will notice that your original Grand Totals on row 44 on the first screenshot, does not match my macro total. ????? Doing a manual calculation on your data, my new totals are correct.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Then run the "CreateTotals" macro.
Sample data before the macro:
| Excel Workbook | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Company: xyz | AFTER | ||||||||||
| 2 | A | B | C | D | E | F | G | H | I | J | ||
| 3 | 80055539 | RA | -15.5 | 0 | 0 | 0 | 0 | 0 | 0 | -15.5 | ||
| 4 | Total | -15.5 | 0 | 0 | 0 | 0 | 0 | 0 | -15.5 | |||
| 5 | ||||||||||||
| 6 | 80123887 | PY | -38,088.00 | 0 | 0 | -38,088.00 | 0 | 0 | 0 | 0 | ||
| 7 | Total | -38,088.00 | 0 | 0 | -38,088.00 | 0 | 0 | 0 | 0 | |||
| 8 | ||||||||||||
| 9 | 80072063 | IN | 56,448.00 | 0 | 56,448.00 | 0 | 0 | 0 | 0 | 0 | ||
| 10 | Total | 56,448.00 | 0 | 56,448.00 | 0 | 0 | 0 | 0 | 0 | |||
| 11 | ||||||||||||
| 12 | 80055636 | AD | -277 | 0 | 0 | 0 | -15 | 0 | 0 | -262 | ||
| 13 | 80055636 | IN | 4,032.00 | 4,032.00 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| 14 | 80055636 | PP | -41.5 | 2,340.00 | 0 | -2,340.00 | 0 | -41.5 | 0 | 0 | ||
| 15 | Total | 3,713.50 | 6,372.00 | 0 | -2,340.00 | -15 | -41.5 | 0 | -262 | |||
| 16 | ||||||||||||
| 17 | 80055599 | AD | 7,264.00 | 0 | 0 | 0 | 7,264.00 | 0 | 0 | 0 | ||
| 18 | 80055599 | IN | 447,433.50 | 320,047.20 | 85,038.60 | 42,347.70 | 0 | 0 | 0 | 0 | ||
| 19 | 80055599 | PP | 54,422.00 | 0 | 0 | -13,140.00 | 67,562.00 | 0 | 0 | 0 | ||
| 20 | 80055599 | RA | -267.5 | 0 | 0 | 0 | -252 | 0 | -15.5 | 0 | ||
| 21 | 80055599 | SH | 1,976.50 | 0 | 0 | 0 | -399.5 | 0 | 2,376.00 | 0 | ||
| 22 | Total | 510,828.50 | 320,047.20 | 85,038.60 | 29,207.70 | 74,174.50 | 0 | 2,360.50 | 0 | |||
| 23 | ||||||||||||
| 24 | 80055688 | AD | 56,986.07 | 0 | 0 | 20,545.41 | 36,440.66 | 0 | 0 | 0 | ||
| 25 | 80055688 | IN | 4,558,844.79 | 3,360,248.59 | 744,555.90 | 454,040.30 | 0 | 0 | 0 | 0 | ||
| 26 | 80055688 | PC | 355 | 0 | 0 | 0 | 0 | 0 | 0 | 355 | ||
| 27 | 80055688 | PD | -72 | 0 | 0 | 0 | -72 | 0 | 0 | 0 | ||
| 28 | 80055688 | PP | 76.5 | 0 | 0 | 76.5 | 0 | 0 | 0 | 0 | ||
| 29 | 80055688 | RA | -11,484.18 | 0 | 0 | -1,461.22 | 10,076.94 | 0 | -240 | -19,859.90 | ||
| 30 | Total | 4,604,706.18 | 3,360,248.59 | 744,555.90 | 473,200.99 | 46,445.60 | 0 | -240 | -19,504.90 | |||
| 31 | ||||||||||||
| 32 | 80055822 | RA | -258.5 | 0 | 0 | 0 | 0 | -252 | -6.5 | 0 | ||
| 33 | Total | -258.5 | 0 | 0 | 0 | 0 | -252 | -6.5 | 0 | |||
| 34 | ||||||||||||
| 35 | 80055603 | IN | 500,889.60 | 500,889.60 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| 36 | Total | 500,889.60 | 500,889.60 | 0 | 0 | 0 | 0 | 0 | 0 | |||
| 37 | ||||||||||||
| 38 | 80110039 | IN | 18,144.00 | 0 | 0 | 18,144.00 | 0 | 0 | 0 | 0 | ||
| 39 | 80110039 | PD | 9,216.00 | 0 | 0 | 0 | 0 | 0 | 9,216.00 | 0 | ||
| 40 | 80110039 | PP | -11,668.50 | 0 | 0 | 0 | 0 | -2,073.50 | 0 | -9,595.00 | ||
| 41 | 80110039 | PY | -17,765.00 | 0 | 0 | 0 | 0 | 0 | -16,014.00 | -1,751.00 | ||
| 42 | Total | -2,073.50 | 0 | 0 | 18,144.00 | 0 | -2,073.50 | -6,798.00 | -11,346.00 | |||
| 43 | ||||||||||||
| 44 | GrandTotal | 36,859,838.45 | 23,974,387.99 | 10,984,394.53 | 1,584,858.74 | 150,130.97 | 88,672.93 | 124,648.61 | -47,255.32 | |||
DATA_US | ||||||||||||
After the macro:
| Excel Workbook | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Company: xyz | AFTER | ||||||||||
| 2 | A | B | C | D | E | F | G | H | I | J | ||
| 3 | 80055539 | RA | -15.5 | 0 | 0 | 0 | 0 | 0 | 0 | -15.5 | ||
| 4 | Total | -15.5 | 0 | 0 | 0 | 0 | 0 | 0 | -15.5 | |||
| 5 | ||||||||||||
| 6 | 80123887 | PY | -38,088.00 | 0 | 0 | -38,088.00 | 0 | 0 | 0 | 0 | ||
| 7 | Total | -38,088.00 | 0.00 | 0.00 | -38,088.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
| 8 | ||||||||||||
| 9 | 80072063 | IN | 56,448.00 | 0 | 56,448.00 | 0 | 0 | 0 | 0 | 0 | ||
| 10 | Total | 56,448.00 | 0.00 | 56,448.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
| 11 | ||||||||||||
| 12 | 80055636 | AD | -277 | 0 | 0 | 0 | -15 | 0 | 0 | -262 | ||
| 13 | 80055636 | IN | 4,032.00 | 4,032.00 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| 14 | 80055636 | PP | -41.5 | 2,340.00 | 0 | -2,340.00 | 0 | -41.5 | 0 | 0 | ||
| 15 | Total | 3,713.50 | 6,372.00 | 0.00 | -2,340.00 | -15.00 | -41.50 | 0.00 | -262.00 | |||
| 16 | ||||||||||||
| 17 | 80055599 | AD | 7,264.00 | 0 | 0 | 0 | 7,264.00 | 0 | 0 | 0 | ||
| 18 | 80055599 | IN | 447,433.50 | 320,047.20 | 85,038.60 | 42,347.70 | 0 | 0 | 0 | 0 | ||
| 19 | 80055599 | PP | 54,422.00 | 0 | 0 | -13,140.00 | 67,562.00 | 0 | 0 | 0 | ||
| 20 | 80055599 | RA | -267.5 | 0 | 0 | 0 | -252 | 0 | -15.5 | 0 | ||
| 21 | 80055599 | SH | 1,976.50 | 0 | 0 | 0 | -399.5 | 0 | 2,376.00 | 0 | ||
| 22 | Total | 510,828.50 | 320,047.20 | 85,038.60 | 29,207.70 | 74,174.50 | 0.00 | 2,360.50 | 0.00 | |||
| 23 | ||||||||||||
| 24 | 80055688 | AD | 56,986.07 | 0 | 0 | 20,545.41 | 36,440.66 | 0 | 0 | 0 | ||
| 25 | 80055688 | IN | 4,558,844.79 | 3,360,248.59 | 744,555.90 | 454,040.30 | 0 | 0 | 0 | 0 | ||
| 26 | 80055688 | PC | 355 | 0 | 0 | 0 | 0 | 0 | 0 | 355 | ||
| 27 | 80055688 | PD | -72 | 0 | 0 | 0 | -72 | 0 | 0 | 0 | ||
| 28 | 80055688 | PP | 76.5 | 0 | 0 | 76.5 | 0 | 0 | 0 | 0 | ||
| 29 | 80055688 | RA | -11,484.18 | 0 | 0 | -1,461.22 | 10,076.94 | 0 | -240 | -19,859.90 | ||
| 30 | Total | 4,604,706.18 | 3,360,248.59 | 744,555.90 | 473,200.99 | 46,445.60 | 0.00 | -240.00 | -19,504.90 | |||
| 31 | ||||||||||||
| 32 | 80055822 | RA | -258.5 | 0 | 0 | 0 | 0 | -252 | -6.5 | 0 | ||
| 33 | Total | -258.5 | 0 | 0 | 0 | 0 | -252 | -6.5 | 0 | |||
| 34 | ||||||||||||
| 35 | 80055603 | IN | 500,889.60 | 500,889.60 | 0 | 0 | 0 | 0 | 0 | 0 | ||
| 36 | Total | 500,889.60 | 500,889.60 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
| 37 | ||||||||||||
| 38 | 80110039 | IN | 18,144.00 | 0 | 0 | 18,144.00 | 0 | 0 | 0 | 0 | ||
| 39 | 80110039 | PD | 9,216.00 | 0 | 0 | 0 | 0 | 0 | 9,216.00 | 0 | ||
| 40 | 80110039 | PP | -11,668.50 | 0 | 0 | 0 | 0 | -2,073.50 | 0 | -9,595.00 | ||
| 41 | 80110039 | PY | -17,765.00 | 0 | 0 | 0 | 0 | 0 | -16,014.00 | -1,751.00 | ||
| 42 | Total | -2,073.50 | 0.00 | 0.00 | 18,144.00 | 0.00 | -2,073.50 | -6,798.00 | -11,346.00 | |||
| 43 | ||||||||||||
| 44 | GrandTotal | 5,636,150.28 | 4,187,557.39 | 886,042.50 | 480,124.69 | 120,605.10 | -2,367.00 | -4,684.00 | -31,128.40 | |||
| 45 | ||||||||||||
| 46 | ||||||||||||
| 47 | GrandTotal | 36,859,838.45 | 23,974,387.99 | 10,984,394.53 | 1,584,858.74 | 150,130.97 | 88,672.93 | 124,648.61 | -47,255.32 | |||
DATA_US | ||||||||||||
You will notice that your original Grand Totals on row 44 on the first screenshot, does not match my macro total. ????? Doing a manual calculation on your data, my new totals are correct.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Code:
Option Explicit
Sub CreateTotals()
' hiker95, 06/25/2010, http://www.mrexcel.com/forum/showthread.php?t=476824
Dim SR As Long, ER As Long, LR As Long
Dim c As Range, firstaddress As String, GTotC As String
Application.ScreenUpdating = False
GTotC = "=Sum("
With Columns(2)
Set c = .Find("Total", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
SR = Application.Match(c.Offset(-1, -1), Columns(1), 0)
ER = c.Row - 1
c.Offset(, 1).Formula = "=SUM(C" & SR & ":C" & ER & ")"
c.Offset(, 1).AutoFill Destination:=Range("C" & c.Row & ":J" & c.Row)
GTotC = GTotC & "C" & c.Row & ","
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
LR = Cells(Rows.Count, 1).End(xlUp).Row
If Right(GTotC, 1) = "," Then
GTotC = Left(GTotC, Len(GTotC) - 1) & ")"
End If
Range("C" & LR).Formula = GTotC
Range("C" & LR).AutoFill Destination:=Range("C" & LR & ":J" & LR)
Application.ScreenUpdating = True
End SubThen run the "CreateTotals" macro.
Upvote
0
Thank you very much hiker95.esSant593,
Sample data before the macro:
Excel Workbook
A B C D E F G H I J 1 Company: xyz AFTER 2 A B C D E F G H I J 3 80055539 RA -15.5 0 0 0 0 0 0 -15.5 4 Total -15.5 0 0 0 0 0 0 -15.5 5 6 80123887 PY -38,088.00 0 0 -38,088.00 0 0 0 0 7 Total -38,088.00 0 0 -38,088.00 0 0 0 0 8 9 80072063 IN 56,448.00 0 56,448.00 0 0 0 0 0 10 Total 56,448.00 0 56,448.00 0 0 0 0 0 11 12 80055636 AD -277 0 0 0 -15 0 0 -262 13 80055636 IN 4,032.00 4,032.00 0 0 0 0 0 0 14 80055636 PP -41.5 2,340.00 0 -2,340.00 0 -41.5 0 0 15 Total 3,713.50 6,372.00 0 -2,340.00 -15 -41.5 0 -262 16 17 80055599 AD 7,264.00 0 0 0 7,264.00 0 0 0 18 80055599 IN 447,433.50 320,047.20 85,038.60 42,347.70 0 0 0 0 19 80055599 PP 54,422.00 0 0 -13,140.00 67,562.00 0 0 0 20 80055599 RA -267.5 0 0 0 -252 0 -15.5 0 21 80055599 SH 1,976.50 0 0 0 -399.5 0 2,376.00 0 22 Total 510,828.50 320,047.20 85,038.60 29,207.70 74,174.50 0 2,360.50 0 23 24 80055688 AD 56,986.07 0 0 20,545.41 36,440.66 0 0 0 25 80055688 IN 4,558,844.79 3,360,248.59 744,555.90 454,040.30 0 0 0 0 26 80055688 PC 355 0 0 0 0 0 0 355 27 80055688 PD -72 0 0 0 -72 0 0 0 28 80055688 PP 76.5 0 0 76.5 0 0 0 0 29 80055688 RA -11,484.18 0 0 -1,461.22 10,076.94 0 -240 -19,859.90 30 Total 4,604,706.18 3,360,248.59 744,555.90 473,200.99 46,445.60 0 -240 -19,504.90 31 32 80055822 RA -258.5 0 0 0 0 -252 -6.5 0 33 Total -258.5 0 0 0 0 -252 -6.5 0 34 35 80055603 IN 500,889.60 500,889.60 0 0 0 0 0 0 36 Total 500,889.60 500,889.60 0 0 0 0 0 0 37 38 80110039 IN 18,144.00 0 0 18,144.00 0 0 0 0 39 80110039 PD 9,216.00 0 0 0 0 0 9,216.00 0 40 80110039 PP -11,668.50 0 0 0 0 -2,073.50 0 -9,595.00 41 80110039 PY -17,765.00 0 0 0 0 0 -16,014.00 -1,751.00 42 Total -2,073.50 0 0 18,144.00 0 -2,073.50 -6,798.00 -11,346.00 43 44 GrandTotal 36,859,838.45 23,974,387.99 10,984,394.53 1,584,858.74 150,130.97 88,672.93 124,648.61 -47,255.32
After the macro:
Excel Workbook
A B C D E F G H I J 1 Company: xyz AFTER 2 A B C D E F G H I J 3 80055539 RA -15.5 0 0 0 0 0 0 -15.5 4 Total -15.5 0 0 0 0 0 0 -15.5 5 6 80123887 PY -38,088.00 0 0 -38,088.00 0 0 0 0 7 Total -38,088.00 0.00 0.00 -38,088.00 0.00 0.00 0.00 0.00 8 9 80072063 IN 56,448.00 0 56,448.00 0 0 0 0 0 10 Total 56,448.00 0.00 56,448.00 0.00 0.00 0.00 0.00 0.00 11 12 80055636 AD -277 0 0 0 -15 0 0 -262 13 80055636 IN 4,032.00 4,032.00 0 0 0 0 0 0 14 80055636 PP -41.5 2,340.00 0 -2,340.00 0 -41.5 0 0 15 Total 3,713.50 6,372.00 0.00 -2,340.00 -15.00 -41.50 0.00 -262.00 16 17 80055599 AD 7,264.00 0 0 0 7,264.00 0 0 0 18 80055599 IN 447,433.50 320,047.20 85,038.60 42,347.70 0 0 0 0 19 80055599 PP 54,422.00 0 0 -13,140.00 67,562.00 0 0 0 20 80055599 RA -267.5 0 0 0 -252 0 -15.5 0 21 80055599 SH 1,976.50 0 0 0 -399.5 0 2,376.00 0 22 Total 510,828.50 320,047.20 85,038.60 29,207.70 74,174.50 0.00 2,360.50 0.00 23 24 80055688 AD 56,986.07 0 0 20,545.41 36,440.66 0 0 0 25 80055688 IN 4,558,844.79 3,360,248.59 744,555.90 454,040.30 0 0 0 0 26 80055688 PC 355 0 0 0 0 0 0 355 27 80055688 PD -72 0 0 0 -72 0 0 0 28 80055688 PP 76.5 0 0 76.5 0 0 0 0 29 80055688 RA -11,484.18 0 0 -1,461.22 10,076.94 0 -240 -19,859.90 30 Total 4,604,706.18 3,360,248.59 744,555.90 473,200.99 46,445.60 0.00 -240.00 -19,504.90 31 32 80055822 RA -258.5 0 0 0 0 -252 -6.5 0 33 Total -258.5 0 0 0 0 -252 -6.5 0 34 35 80055603 IN 500,889.60 500,889.60 0 0 0 0 0 0 36 Total 500,889.60 500,889.60 0.00 0.00 0.00 0.00 0.00 0.00 37 38 80110039 IN 18,144.00 0 0 18,144.00 0 0 0 0 39 80110039 PD 9,216.00 0 0 0 0 0 9,216.00 0 40 80110039 PP -11,668.50 0 0 0 0 -2,073.50 0 -9,595.00 41 80110039 PY -17,765.00 0 0 0 0 0 -16,014.00 -1,751.00 42 Total -2,073.50 0.00 0.00 18,144.00 0.00 -2,073.50 -6,798.00 -11,346.00 43 44 GrandTotal 5,636,150.28 4,187,557.39 886,042.50 480,124.69 120,605.10 -2,367.00 -4,684.00 -31,128.40 45 46 47 GrandTotal 36,859,838.45 23,974,387.99 10,984,394.53 1,584,858.74 150,130.97 88,672.93 124,648.61 -47,255.32
You will notice that your original Grand Totals on row 44 on the first screenshot, does not match my macro total. ????? Doing a manual calculation on your data, my new totals are correct.
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Code:Option Explicit Sub CreateTotals() ' hiker95, 06/25/2010, http://www.mrexcel.com/forum/showthread.php?t=476824 Dim SR As Long, ER As Long, LR As Long Dim c As Range, firstaddress As String, GTotC As String Application.ScreenUpdating = False GTotC = "=Sum(" With Columns(2) Set c = .Find("Total", LookIn:=xlValues, LookAt:=xlWhole) If Not c Is Nothing Then firstaddress = c.Address Do SR = Application.Match(c.Offset(-1, -1), Columns(1), 0) ER = c.Row - 1 c.Offset(, 1).Formula = "=SUM(C" & SR & ":C" & ER & ")" c.Offset(, 1).AutoFill Destination:=Range("C" & c.Row & ":J" & c.Row) GTotC = GTotC & "C" & c.Row & "," Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address <> firstaddress End If End With LR = Cells(Rows.Count, 1).End(xlUp).Row If Right(GTotC, 1) = "," Then GTotC = Left(GTotC, Len(GTotC) - 1) & ")" End If Range("C" & LR).Formula = GTotC Range("C" & LR).AutoFill Destination:=Range("C" & LR & ":J" & LR) Application.ScreenUpdating = True End Sub
Then run the "CreateTotals" macro.
I tested the macro on my original file and I got a debug error on line of code -
Range("C" & LR).AutoFill Destination:=Range("C" & LR & ":J" & LR)
I will try it again. I need to understand all the line codes.
Upvote
0
It is my stupid mistake, I forgot to delete that text note on A46. Thank you very much for your expertise that you shared to help me. It is working. My other question is - will this macro will work with array of sheets or I have to activate each sheet and run that macro?
Upvote
0
esSant593,
Right now the macro will work on the active worksheet.
Do you need the macro to work on all worksheets in the workbook?
Or, do you need the macro to work with array of sheets?
Please explain in more detail.
will this macro will work with array of sheets or I have to activate each sheet and run that macro?
Right now the macro will work on the active worksheet.
Do you need the macro to work on all worksheets in the workbook?
Or, do you need the macro to work with array of sheets?
Please explain in more detail.
Upvote
0
The workbook I am trying to set up the macro have 4 worksheets with varying number of data with Total lines, some with 200 Totals & some have 1000 Totals. What is the best way? I preferred to work with array of sheets if not much to ask.
Upvote
0
Similar threads
- Replies
- 2
- Views
- 245
- Replies
- 3
- Views
- 61
- Replies
- 4
- Views
- 166
