Adding a Row and a Column with the same VBA Button

[gary_feesher]

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

[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

[gary_feesher]

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

[Philb1]

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.

[gary_feesher]

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

[Philb1]

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.

[gary_feesher]

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

[Philb1]

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.

[gary_feesher]

Hi Phil,

Thanks for the updated comments. I have attached a workbook with the sample data. Thanks for your help!

I think we are very close. One issue I am seeing is that when the "Add Feature" is clicked, a new column is added next to the Down column. Also, in the part of your code:

The cell reference needs to go from G4 to H4 to I4, and so on, for each Added Feature. This is keeping it at G4 for each button click. Any idea how to fix this?

Excel Workbook with Sample Data

[Philb1]

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?

[Philb1]

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?

[gary_feesher]

Hey Phil,

I believe that all of my problems are now solved.

I changed:

TO:

Thanks so much for your help! You really helped me solve my problem and achieve my goals!

[Philb1]

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

[Philb1]

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