VBA code to replace formulae in cells and then re-enter same formulae again after run

Hello Guys,

I need some expert VBA code help. This is what I need to do:

(1) I have cells that contain formulae. These formulae refer to other ranges in workbook and the cell addresses themselves are referred by other parts of the workbook.
(2) I need to do a scenario testing by setting all these formulae containing cells to numeric 0.
(3) I have a range of output cells whose inputs are the above cells (which had formulae but now contain 0). Once the inputs are set to 0, I need to hard copy (values only) the output range into another row (serves as future reference to what happens when certain inputs are set to 0).
(4) At the end of this scenario testing I need to make sure all cells whose contents were replaced with a numeric 0 have their original formulae repopulated i.e. as if the workbook was never tempered with!

I have attached a very simplified version of the workbook I am using. The Growth Tab is where the cells which need 0's are located. The Proj_Count Tab is referenced by the formulae in Growth Tab. Finally the Output Tab contains the output row which references the cells from Growth Tab.

I hope all this is clear as mud! Please let me know if I need to provide further explanations or more simplifications.

Thanks so much all.

Happy Sunday.

Yusuf

Please Login or Register  to view this content.

Hi @porucha vevrku and @jindon,

Wow! This is fantastic...This is exactly what I was hoping for... the wonders of VBA.

@porucha vevrku - I preferred your "Longy" method as the formulae in each cell vary (they make references to different ranges as you move thru the cells). I think the "Shorty" just saves the formulae that was in the first cell of each row. Though my only concern is if the Sub A_formulae_resetting_constant is run twice without running Sub B_formulae_restoration_memory in between then the original formulae are lost completely - something I did by accident as I was playing around. Anyway I can neatly combine the 2 Subs into one to prevent erasing original formulae?

@jindon - your code works like a charm!

Thank you to both of you - this really saves me a huge amount of work when doing scenario testing! Much much appreciated.

Have a great week.

Yusuf