Avatar of Bright01
Bright01
Flag for United States of America asked on

Macro for changing Formulas to Values

EE Proessionals,

I  have a rather complex Worksheet that I'm putting together to calculate Life-TIme-Value.  I need a macro that I've started to write but it doesn't work.  The Macro is in Module 1, called Sub Formula_to_Absolute_Click(). What it is suppose to do is take any value (created by the formulas) and change it from formulas to "absolute values" and by pressing the button again, it needs to return to the original formulas.  The Range should be E5:Z9.

That's it!   Much thanks in advance.

B.
Macro-for-Formulas-to-Absolute-a.xlsm
Microsoft Excel

Avatar of undefined
Last Comment
Bright01

8/22/2022 - Mon
JP

In order to do this you would have to store the original formula in another cell. Otherwise when someone saved the file you would be left with whatever data was last in the cells. So if you converted to the absolute value and the file was saved you would have no way to go back to the formulas.
JP

The other question is why? Why do you need the absolute value and not the result of the formula?
Bright01

ASKER
Peetjh,

Thank you for the reply.  The reason is that I'm trying to create two scenarios.  Scenario 1 says that we assume that all of the values are = across the elected number of years (formulas).  Scenario 2 says, there are differences in the Rev., change in COGS or Fixed costs in different years and you have to enter different amounts (values).  That's as simple as it gets.  If there is a different way to do  that, "enter values, where there are formulas, then that may be a different approach to the same situation.

Any ideas?

B.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
JP

I think this may be close to what your looking for. All I did is change the checking of the btn.caption the way it was setup it would never be customized. I was thinking your formulas may be much more complex than something that could be easily copied to a range. Oh I also changed the formula so it would stay consistent across the columns is was not set for absolutes.
Macro-for-Formulas-to-Absolute-a.xlsm
Bright01

ASKER
peetjh,

Maybe I wasn't very clear.  If you go to a cell that has been populated by a copied formula and you want to put in a change; Let's take F5.  It's $500.  If I want to make that $600, it won't let me.  The idea here is that if one condition ("consistent") is selected, I can't change it; however, if I select "Values", I can substitute a number for a formula and it will behave properly.  The other challenge is that when I reset the model, I want to put the copy formula condition back on.

Make sense?

B.
JP

You have another macro that runs when a cell is changed it is overwriting the changes you are making to F5
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bright01

ASKER
peetjh,

Yes... I think you are right.  I do not know how to stop it from reacting automatically.  The idea was that when you hit the button to change from consistent (with formulas copied for "consistency") to "values" (with formulas disabled -- so you could put in values); that's what I thought would solve the problem and create the appropriate situation.

Make sense?

B.
JP

Here, I have commented out the macro that runs when values are changed but not sure if you need it still.
Macro-for-Formulas-to-Absolute-a.xlsm
Bright01

ASKER
peetjh,

This doesn't work.  Let's try this in a different way..... I have attached a newer file that actually allows for a reset.  What I am trying to do is have BUTTON 2 create a condition where I can input VALUES (instead of formulas) in the Rows 5, 6 and 8 beginning in Column E and going as far as the dates go out (as selected in Cell D3).  When BUTTON 2 is hit again, the formulas are put back in and the values are replaced.  A reset does exactly what has now been built in.

See if that makes sense to you.

B.
Client-LTV-Assessment-v2.xlsm
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
JP

k, I think this will work, you only need to click the button to change back to formulas. You can type in any values over the formula you want.
Client-LTV-Assessment-v2.xlsm
Bright01

ASKER
peetjh,

Thanks for the note.  When I click the button, nothing happens.... and the formula doesn't come back into play. So if I go to G5 and change the formula =G4 to $700, it changes the cell to $700. But it doesn't go back to the formula when I click the button.

Once I have this working right, I have a related question to the formulas.  If you look at the formula in a column, you will see that the formulas in 7 and 9 are different then the others.... instead of a formula of = previous column, it's actually E5+E6=E7 and E7+E8=E9..... I'm looking for those formulas to remain in place and not be changed.  

Can you take a look at the Fx macro you created?

Thank you,

b.
ASKER CERTIFIED SOLUTION
JP

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bright01

ASKER
Peetjh,

Great job!  Just integrated it into the larger spreadsheet and it worked great.  I'll be adding a follow on question shortly since I'm going to get stuck on the next phase of this model.  Hope you will pick it up.  Thanks again.  Appreciate your efforts.

B.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.