Multiple Level Addition using Macros

Hello ajayketkar,

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi Ajay,

Try this:

Please Login or Register  to view this content.

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

Thanks xladept,
I have tried the code and it works. But, I have few more requirements. How do I retain the formulas in the calculated cell. After running the code I can see that it is calculating the value, but not retaining the formula.
Also, In my further requirement, my content tree is going to expand till 8 level (A column) for column as well as it may go up to 1000 rows. Another thing is that, if my weight values(currently in C column) are in another column, where do I change the code to calculate the values accordingly?

One more thing xladept,
I have noticed that, the loop is considering ''assly'' as key word to distinguish between assemblies and components. I want them to be distinguished only on the basis of successive level difference from column A irrespective of their description.

Hi Ajay,

Thanks for the rep!

I have tried the code and it works. But, I have few more requirements. How do I retain the formulas in the calculated cell. After running the code I can see that it is calculating the value, but not retaining the formula.

The code uses accumulators(ST for subtotal and GT for grandtotal) - there is no formula.

C is the the third column so where I have a "3" is where you would change the code:

Please Login or Register  to view this content.

What, exactly, should we key on?

Hi Ajay,

What will the data look like? What will be different from your sample and what will be the same? Can you post a more relevant sample?

Hi xladept,
I will post the relevent sample soon. Timebeing please see the following macro code. This is used for autogrouping. Can you refer this and update the macro for calculation based on level as we had done it in the previous example.

Here is the macro

Please Login or Register  to view this content.

Regards

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



* Please attend to this

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



* Please attend to this