Script required to sum columns for specific labels, and insert totals into different adjacent columns

gregfthompson
gregfthompson used Ask the Experts™
on
Excel worksheet - Sum of specific columns against specific rows and inserts into adjacent columns.

1. Insert row after last Newspaper title
2. sum column R
3. insert column R total into Column G
4. sum column S
5. insert column S total into Column j
6. insert column A bottom row content into all rows above so that any amount keyed into the bottom cell will appear in all cells above.
7. insert column H bottom row content into all rows above so that any amount keyed into the bottom cell will appear in all cells above.
8. insert column K bottom row content into all rows above so that any amount keyed into the bottom cell will appear in all cells above.
9. insert column M bottom row content into all rows above so that any amount keyed into the bottom cell will appear in all cells above.
10. insert column O bottom row content into all rows above so that any amount keyed into the bottom cell will appear in all cells above.
11. insert column Q bottom row content into all rows above so that any amount keyed into the bottom cell will appear in all cells above.
12. repeat for each different newspaper title

Example file attached - Sheet1 with original worksheet. Sheet2 with example.
EENewspapersExample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
A few questions:

1 Do you mean insert a new row between each newspaper title in column E?

2 Which row should the totals from column R & S go in columns G & J?

3 What's to happen to the existing values in columns H, K, M etc.?

Author

Commented:
Hi Norie,

Thanks for you reply.

1.  Yes please - to allow the total for R and S to be shown.
2. Is it possible to have the total for R repeated for each row in G column that has the newspaper title for that R total? And same for the total for S to be repeated in J column for the newspaper title that is included in that S total?
3. The idea is that when the values in H, K, M etc change, they can be changed by amending the value in the bottom cell for each column so that it then is auto amended up to the top.

Thanks for your help.

Greg

Author

Commented:
I've amended the example to 5 steps:
EEExample2.png
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please check your steps 3 and 4 in EEExample2.png. The columns in 3 seem wrong, and the row references in both don't seem to match the data in the workbook, as the first change in column "E" is between rows 163 and 164.

Author

Commented:
Thanks for your note. Sorry about the sloppy example. Have amended steps 3 and 4 in the attached.
EENewspapersExample3.xlsx

Author

Commented:
Hi Martin,
Have you had a chance to look at the revised example?
Thanks,
Greg
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry but I completely missed your update. I'm looking at it now and will be back soon.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this.
29144730.xlsm

Author

Commented:
Thanks Martin.

Apologies for delay in reply.

I clicked on Sum Columns, and it stopped at line 23. (screenshot attached)

Did I do something incorrect?

Thanks,

Greg
29144730.xlxm-debug-screenshot.png
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this version and if it fails, tell/show me what the error message says.
29144730a.xlsm

Author

Commented:
Thanks heaps Martin,

It appears to work (no debug error panel to show), but the sum totals are not in a new row, they appear in the first row of the next newspaper.

See attached row 164.

Can you insert a new row for the sum totals. Everything else appears to be perfect.

Thanks,

Greg
29144730a.xlsm
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this.
29144730b.xlsm

Author

Commented:
Fabulous. Thanks so much.

Hi Martin,

It appears that something is not quite right. I've amended the script (there's an extra column added so the totals columns moved from R & S to S & T.

But when it runs, it appears to insert rows at the top. Full file is attached.

Thanks,
Greg
29144730C.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial