+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    08-19-2015
    Location
    Toronto, Canada
    MS-Off Ver
    MS 2016
    Posts
    13

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    1,601

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

    Two variants:

    1. "Shorty" - formulas stored as constant strings
    Please Login or Register  to view this content.
    2. "Longy" ... - formulas stored in memory
    Please Login or Register  to view this content.
    Last edited by porucha vevrku; 11-19-2017 at 05:40 PM.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,215

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

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    08-19-2015
    Location
    Toronto, Canada
    MS-Off Ver
    MS 2016
    Posts
    13

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace test in formulae within cells using VB
    By stevetalaga in forum Excel General
    Replies: 1
    Last Post: 12-23-2015, 01:39 AM
  2. [SOLVED] Searching TEXT in formulae, rather than results of formulae
    By AndyE in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  3. Searching TEXT in formulae, rather than results of formulae
    By AndyE in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  4. Searching TEXT in formulae, rather than results of formulae
    By Niek Otten in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  5. Searching TEXT in formulae, rather than results of formulae
    By AndyE in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. [SOLVED] Searching TEXT in formulae, rather than results of formulae
    By AndyE in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] Searching TEXT in formulae, rather than results of formulae
    By AndyE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2005, 06:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1