insert sum formulas based on worksheet data

[SteveM99]

Dear VBAers

I am trying to write the code to insert the sum formulas in subtotal rows and one total column. I have attached the sheet I am working with. Basically a 12 month worksheet that is exported into excel with values. The highlighted yellow cells show what I am trying to insert (this is the basic layout I have to start with although it will fluctuate based on the data rows). Also need to work on the net row at the end but I can take a stab at that later. And, I will need to reference the report sheet I am inserting the formulas into as the report sheet is part of bigger macro file.

[p45cal]

The attached is a bit of a cheat, and may not be robust enough for you.
There's a button in the vicinity of cell P6 of the sheet REPORT (2), which is how I imagine your sheet starts out as (without totals).
Press the button, only once mind, and see if it gives the results you want.

There are other ways…

[Paul_Hossler]

I'm confused

What does the report look like before any desired changes, and what would you like it to end like?

I see ...

... different formats and spacing (row 43 6000's and 57 7000's),

... 2 formulas (O10 and N16),but the rest of col O and col N are values

[SteveM99]

p45cal,

Words cannot express how awesome this is, thank you. I spent the better part of my morning trying to figure out how to do this using loops, offset, copy pasting, end up, etc using vba. I opened up your code and its nothing I have every used or seen before. Actually I knew the .selection and .delete parts. I thought I knew some stuff but I just realized I haven't even left the garage. Furthermore I really appreciate your help because I felt guilty during the day that I just uploaded a file and asked for someone to help code it in lieu of providing what code I knew and adjust thru the recommendations. I was actually going to delete my post but so I glad I did not and you helped me. Thanks a bizzillion and loop that with some code for another bizzilion times!!! I believe this is not the first time you have helped me, thank you for all your efforts. Now I am going to study your code and google what the components do so I can add it to my small knowledge base. And finally, I will never upload a file without having some code, however rudimentary. This forum is a great place!!! Thanks!

[SteveM99]

Paul,

The report comes as is (as values) except for the 2 cells colored yellow. That is where I entered the formulas I want (as an example for what I wanted). I was looking for sum formulas in the total rows (rows 16,20,42,54,55,63 and 65 columns C to O) and column O (all rows except blank rows - these are cross foots - old accounting term). I just cannot figure out how to define and insert the sum formulas using loops and the end up down codes. Pcal45 provided great code which does the job except for the total row but I think I can figure that out (plus I did not ask for that row for help, so he did perfect). If you want to provide some code I would appreciate that because the more code I can see and work with the more I will learn. I work with a lot of reports that come as values and I need go in and macro in the formulas where needed. The issue is the files are all different so I can have static code, rows and columns change. If you dont want to spend your time because I have a working solution that is fine I understand. Thank you for looking at my file and asking for clarity on the file and what I was looking for. I will post this thread as solved tomorrow night if you have time before then cool or tell me to wait another day as you may have more questions.

[p45cal]

An exercise to demonstrate flakiness:
Before clicking the button, delete the contents of cell C30.
Look at the mess that ensues.
Easy enough to cater for 'though.

[Paul_Hossler]

Paul,

The report comes as is (as values) except for the 2 cells colored yellow. That is where I entered the formulas I want (as an example for what I wanted). I was looking for sum formulas in the total rows (rows 16,20,42,54,55,63 and 65 columns C to O) and column O (all rows except blank rows - these are cross foots - old accounting term). I just cannot figure out how to define and insert the sum formulas using loops and the end up down codes. Pcal45 provided great code which does the job except for the total row but I think I can figure that out (plus I did not ask for that row for help, so he did perfect). If you want to provide some code I would appreciate that because the more code I can see and work with the more I will learn. I work with a lot of reports that come as values and I need go in and macro in the formulas where needed. The issue is the files are all different so I can have static code, rows and columns change. If you dont want to spend your time because I have a working solution that is fine I understand. Thank you for looking at my file and asking for clarity on the file and what I was looking for. I will post this thread as solved tomorrow night if you have time before then cool or tell me to wait another day as you may have more questions.

If you're happy

I was just confused because there were 2 cells that needed formulas, but I didn't see why the similar cells came with values

[SteveM99]

An exercise to demonstrate flakiness:
Before clicking the button, delete the contents of cell C30.
Look at the mess that ensues.
Easy enough to cater for 'though.

Wow, that is a mess.

[SteveM99]

An exercise to demonstrate flakiness:
Before clicking the button, delete the contents of cell C30.
Look at the mess that ensues.
Easy enough to cater for 'though.

Just posted another thread on same worksheet but now working on inserting a sum formula to add up the variable rows (the total rows). Not sure how to approach non-contiguous rows.