VBA Code to Total Sections with Varying Number of Rows

I would REALLY like some help with creating a code for section totals and then another code that adds a ?total? row. I just started learning about Macros and VBA codes last week, so this is all pretty new and a little overwhelming to me. I apologize in advance for the long description, it?s hard to explain verbally, let alone capture in words.

I've attached a sample spreadsheet. I've also attached a snapshot, in case you don't want to open the file.

For the section totals, those formulas need to go in columns G/I/K/N in the highlighted and bold rows. The problem is, there could be 1 section, or up to 5 or 6 sections, each having a different number of rows. For example, I would like the section total formula in cells G3/I3/K3/N3 to total up the rows below (items 20/30/40), but stop at the last part number before the next section. I would like to do the same for all sections and option sections, rental sections don?t have to have totals.
There will always be at least 1 section and 1 option section in each quote.
I added ?higher level item? data in column Q. I don?t currently have that information pulling in, but I can if it will make this easier. The ?higher level item? will identify which section parts belong to in a quote. For example, Section A in Quote 1 is Item 10, so every part in Section A will have a higher level item of 10. As you can see Quote 4 also has a Section A that is item 10. Part of the VBA code I currently have will insert 2 rows between quote numbers that are different, so hopefully that helps too.

For the total row, I would like to insert 2 rows above the first option section in each quote. In the first row that was inserted is where I would like ?total? in column D with right aligned. I would like the total formulas in columns G/I/K/N to add up the section totals.

For the full scope, I will also need formulas added to the financial summary tab in columns E/F/G/N that will pull from the total row for each quote. Information in the first row (row 4) would pull the total for the first quote in the BOM in tab. Second line would pull the information from the second quote and so on.

nankw83 Thank you so much for the quick reply. The formula works ALMOST perfectly! The only issue I see is the sum formula in the last Section before the Option Section. In those formulas, they include the Option Sections and Rental Sections in their totals.
There also isn't a formula for the Option Sections, but I figured once we get the Section formulas down I'll try to recreate that for the Option Sections.

Just 'SECTION*' and 'OPTION SECTION', no totals needed for 'RENTAL SECTION'. The range will need to be C rather than D because the description in column D will change sometimes, but column C will never change.

That is weird because I can still see your previous code. Anyway, I tried the code and it added the sum formulas to the option sections. However, the last option section in each quote goes past the last part in that option section. I've attached a couple snapshots to help explain.

No sir, the option section formula should only include the parts in the specific option section. So basically, it would need stop before the next bold section. So in snapshot 1 that formula would need to stop at row #14. The formula in snapshot 2 would need to stop at row #29. Rental sections don?t need to be totaled, but if it will make this VBA code easier, totaling the rental section won?t be an issue.
I know this hasn?t been easy, but I really appreciate your patience and willingness to help me.

I don?t have access to that file right now since I?m not at home, but see below for what the formula should be for rows that are incorrect. I just don?t want to cause confusion because those formulas will change for every quote that is done, since the number of rows will always be different.

Row 12, Cell G12: =SUM(G13:G14)
Row 34, Cell G34: =SUM(G28:G29)

Originally Posted by Sawyer84

Row 34, Cell G34: =SUM(G28:G29)

Hello. Sawyer84.
Your clarification didn't help much because you were wrong to mention row 34.

What oeldere asked you to do is upload a second workbook to the Forum that has three sheets:

- BOM,
- Co. Cost, and
- expected BOM

In this last sheet you will place the expected formulas in the cells: G24, G27, G30 and G33.

Goodness, yes, you?re right. That?s what I get for trying to do this on my phone. Correction below. I?ll try to get that spreadsheet uploaded when I get home later today. Rental Sections on rows 30 and 33 don?t need a formula, but if it makes creating the correct VBA code easier, having formulas for the rental sections won?t be an issue.

Row 12, Cell G12: =SUM(G13:G14)
Row 27, Cell G27: =SUM(G28:G29)

Okay, I think I figured out a way to make the VBA code that nankw83 created. The only formula that is slightly wrong is the very last section on the very last quote. So that will be easy enough to fix or just ignore since the last section is typically a rental section, which don't need a formula anyway. I will mark this as solved. I tried to post the code, but it wouldn't let me, so I've attached a text file and a snapshot.

nankw83 I am incredibly thankful for all of your help with this.

Mmm...
I see that you assume that the value of cell G7 should be 3.

I ask you: shouldn't it be 10 that represents the sum of all Section B (G8 to G14)?

Negative, Section B only consists of the 2 rows below it, rows 8 and 9. Basically, every row that is in bold is it's own section. I've attached a snapshot of what needs to happen next, which I was going to post in a new thread. I just wanted to mention it to you so you have the full picture and may help explain things. Only rows that have Section A, Section B, Section C, etc., in column C, are included in the total. Anything that has Option Section or Rental Section in column C, aren't included in the total. Like I said, my next thread will be about creating a VBA code that will insert 2 rows above the first option section in every quote and that puts the Total text in column D and formulas in columns G/I/K/N that adds the section totals above it. I already have the conditional formatting that will bold and color a row with the text Total in column D

That wasn?t my goal and I apologize if I wasted anyone?s time. I mentioned in my very first post (2nd to last paragraph) that my next goal would be inserting a row that calculates the total. I know my first post was long, so I can understand you not making it all the way to the end to see that information.

I've attached the spreadsheet with the sheets like you asked. The original data doesn't have the rows inserted between the quotes, I created a VBA code to do that. However, I found it made the formula work easier if I added the rows between quotes after the sections were totaled.

As this thread is already finished and -probably- nothing that we have helped you will serve the purposes of the new requirement you have (the most important thing is that your original data does not come with empty rows as I imagined), then you better start a new thread and upload this last workbook.

Tip: Don't rush to end the thread or give reputation early!

Sawyer84,

try

Please Login or Register  to view this content.

jindon, thank you for that. It is close to working perfectly. The only thing it didn't do correctly was the total formula. Instead of adding only the section totals (rows 3 & 7), it added everything. I've attached a snapshot of what it did (1st snapshot) and then what it should be doing (2nd snapshot).

Didn't notice the formula in top of each SECTION.
try

Please Login or Register  to view this content.

Holy smokes, that worked perfectly!! Thank you so much!

You are welcome and thanks for the rep,

When you need to Sum only the summed row(s) within the specific range, Subtotal function should be the way to calculate.

That actually makes a lot of sense after seeing how you did that. I appreciate the tip.

jindon, after a second glance I had to modify your code slightly since it was putting the subtotal in columns O and P. It works, but columns O and P in the new Total row are blank. Is there anyway to modify this code to copy the formula either above or below? See below for the pre-loaded formula in those columns.

Column O formula: =(N10-G10)/N10
Column P formula: =(N10-I10)/N10

Please Login or Register  to view this content.

@jindon, sorry for the back to back messages, but I just tried this your original code on an existing spreadsheet with multiple quotes and it didn't insert the Total rows correctly, see snapshot below. Often times there are multiple options sections in one quote and I think that is what's causing this issue.
Snapshot 1 (Quote 1): Rows 15 and 16 shouldn't be there
Snapshot 2 (Quote 4): Rows 42 and 43 shouldn't be there

See if this works as you want.

Please Login or Register  to view this content.

That looks like a winner. I'll continue to keep testing it. Thanks again for all of your help.

@jindon, I did find one issue. Sometimes there is only 1 section on a quote, it's typically Section A, but sometimes it might be a different Section. In this scenario, I had a spreadsheet that had a quote with only Section D and it wouldn't put spaces between the quote above it, or add the total row. See snapshot below. There should be 2 rows above row 18 to separate quote 1 and quote 2 and then there should be 2 rows above row 21, which include the total.

I will have a look at it sometime tomorrow, hopefully.

Thank you very much

Okay, so I'm kind of lost now and I will COMPLETELY understand if you want to give up on this. I know you've already spent a lot of your time on this. However, if you are still willing to help, that would be incredible. So the issue with your code is the first section in each quote includes the total of the entire quote, so it acts just like the total row. The other issue is that there isn't a formula for each section and option section. So, I modified your other code below and the only issue with this code is that it only works correctly if the first section in a quote is Section A. If it could be modified to work if the first section in a quote is Section B, Section C, Section D, Section E, Section F, or Section G, then that would be great.

Please Login or Register  to view this content.

Working with picture is too hard.
If you upload a workbook with all the possible situations that will occur and the result that you want, I will look at it tomorrow.
If you don't want to do it, just ignore this post.

I will absolutely do that. I will upload it tomorrow morning my time, which will probably towards the end of your day.

Okay, I've uploaded the spreadsheet. The 'Expected BOM' is what I would like it to look like.

Let's see if I missed anything.
This code also accepts with or without any OPTION block(s) within a QUOTE.

Please Login or Register  to view this content.

Awesome, I'm checking it out now.

Well it's official, you're a genius! lol. For real, I've ran some tests on real life quotes and I haven't found an issue. I literally can't thank you enough for all of your hard work, I don't believe a lot of people would go above and beyond like you did. Thanks again.