Adding a Row and a Column with the same VBA Button

I have a template/tool that I am building that creates a Waterfall chart based on data inputted from one of my employees. The table begins at "A2".

The data in the top table is manually input. The Totals are calculated by the Sum.

The data in the table underneath has formulas in the Values, Totals, Blank, Up and Down columns.

I need the 'Add Feature' button to not only add a column in the top table (and make sure the Total is summing with the new Feature, too). I also need a Row added in the bottom table (above "Product 2"), that follows the same formula rules as the other cells in the table.

At the moment, my 'Add Feature' button only adds a Column to the upper table. It also adds a row to the bottom table, which is good, but when I click it for a second time, it doesn't add a new row.

My current code is as follows:

Please Login or Register  to view this content.

Origina:
Original.JPG
Desired after first click of "Add Feature"
plus1.JPG
Desired after second click of "Add Feature"
plus2.JPG
Formulas
formulas.JPG

Hi Gary
There's a fair bit of selecting in your code that doesn't need to be there. Can you attach a workbook with dummy data?
You may have trouble with that, I have done for months now & have to use a dropbox link for people to be able to get the files

Originally Posted by Philb1

Hi Gary
There's a fair bit of selecting in your code that doesn't need to be there. Can you attach a workbook with dummy data?
You may have trouble with that, I have done for months now & have to use a dropbox link for people to be able to get the files

Here is a Google Drive link to the .xlsm file: Waterfall Chart Generator Sample

Below is the code that will insert a column & row.

As you have just the 1 worksheet you can get away with not qualifying the code. It's a good idea to tell VBA which sheet you want the code to run on otherwise it can destroy the workbook. I can tell you that from experience lol.
I've qualified it using the sheets codename Sheet4. I don't know what the clearcontents code was for so I've commented it out, same with Cells(1,1).select. It doesn't update the formulas into the new row because I think you have some sorting out to do there. there's formulas referring to rows that will never have any data
Let me know how you go
Cheers

Please Login or Register  to view this content.

Hey Philb1 ,

This is a fantastic help for me. One problem I am seeing is that my value in the Totals column for Product 2 is not getting pulled down when a new Feature is added.

Do you know how I can ensure that the Product 2 Total is moved down, as that row is moved down from new Features being added?

Thanks!

Nick

Try this.
If it's not right let me know & attach an example of what you're wanting it to do & I'll look at it in the morning
Cheers

Please Login or Register  to view this content.

Thanks for your help, but it seems to have created some issues that I've shown in the photos below.
Before:

Start.JPG

After clicking "Add Feature" 3 times:
End.JPG

The macro is filling across & down the formulas you have in place and I said in the last post, there's problems. The reason you get the #VALUE error is because the formulas are trying to sum text & numbers, look at the formulas in the Feature 6 row & you'll see what I mean.
I don't know what you're trying to do, so if you fill a table up to say Feature 7 with the formulas how you want them etc & post a workbook rather than pictures, I'll have a look
I've made a couple of changes to see if it improves things, it hasn't fixed them yet

Please Login or Register  to view this content.

Hi Gary
Are the Up & Down columns supposed to repeat?
I'm wondering if the Min & Max formulas are supposed to repeat in pairs and move along the top feature table 1 column at a time.
Looking in E & F columns the formulas refer to column B. Does that mean columns G & H refer to column C?

Hi Gary
Are the Up & Down columns supposed to repeat?
I'm wondering if the Min & Max formulas are supposed to repeat in pairs and move along the top feature table 1 column at a time.
Looking in E & F columns the formulas refer to column B. Does that mean columns G & H refer to column C?

Hi Gary
That's good but I thought you didn't want extra columns inserted. I've done another example for you to try
Let me know if it's what you wanted
Cheers

https://www.dropbox.com/s/mo3ubv60i9...able.xlsm?dl=0

Me again
I made a minor change to the code. I thought of it after I turned the computer off last night. If you've downloaded it before reading this, download it again so it updates the Product 2 formula properly. Hopefully it's all good now