Edit Macro to Auto Populate Formula's

[Cansa]

Hi All,

currently i have a Macro that auto fills formulas in Columns AL and AO, it will only auto-fill these when there is data enter into Col A, which is exactly what i need it to do....so far this only works for "Die 1", I would need this to work for any Dies that are added (eg, Die 2 [AV and AY], Die 3 [BF and BI], etc...) the # of Dies will constantly be being updated (added or deleted, there will also be more than 3 dies at times), so is it possible to edit the Macro to auto-fill these formulas when there are more Dies and when Data is entered into Col A?
Here is the formula Currently and the Document is also Attached for Reference...

latest bom.xlsm

Please Login or Register  to view this content.

Regards,
Cansa

[tehneXus]

Hi,
try:

Please Login or Register  to view this content.

[Cansa]

Thank you TehneXus! That formula worked perfectly! I have one last issue with this document though.... is there a way to edit this formula, to only allow you to select the "Setup Die Table" button once? any time that the Macro has another prompt attempt after the first time its ran would need a Pop up Display saying "Die Tables are Only allowed on the Initial Setup", and the Macro should not be ran...I hope this makes sense. Please let me know if you need clarity.

latest bom.xlsm

here is the Macro,

Please Login or Register  to view this content.

Cansa

[tehneXus]

Hi,

to prevent an action to be done more than once write a value to a not visible cell when ran first, then check that cell. I've inserted a new sheet which is "Very Hidden", in A1 of this sheet the value "TRUE" will be written when the button is clicked, if you try to run it again it will stop you:
latest bom.xlsm


Regards

[Cansa]

Hi TehneXus,

I figured i would bring this up to you since you know this document a bit from working on it and since you are one of the very few who know Macro Coding so well....I have a few tweaks that our Tooling Dept needs done for it..I was hoping that you might know how to adjust the Macro to meet these needs,

There are columns that need to be inserted per Die on the BOM sheet, Col Q is labelled "1" for Die 1. We would also need Columns added for the Number of Dies entered (e.g. Col R labelled "2" [depending on the number of dies entered in B5 on the Input-Output Sheet...]).

The Formulas in Col N on the Input-Output Sheet are not correlating to the Die Tables that are updated...All of the other formulas are correlating okay. (I'm hoping this would be an easy fix).

Thirdly, I need to make sure that the Macro, and the Formulas still update properly, even after Columns are Added/inserted on the BOM sheet. So an issue for example, if i insert a column after Col A...then enter 5 Dies on the Input-Output Sheet and run my Macro, the formulas no longer correlate on their Die Tables...

Your help is greatly valued!! Please let me know if you need any clarification on anything here....

Best regards,
Cansa