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

Changing a % to $ and $ to %

EE Professionals,

I'm looking for a simple macro that is driven by a change to a cell (see Sample; Cell E5).  If the Condition is set to % then the input cell is represented as a % and the calculation is made as D5 * E5% and placed in F5.  If the button is pressed and changed to $, then the value in cell E5 is changed to a $ value and F5 is recalculated as D5-E5$ and placed in F5.

It needs to be separated into 2 subroutines; one for % and one for $ change so they can actually be called from other macros to impact the same cells.  Big cudos for actually making the button change from $ to % when clicked and selecting the right macro.

Make sense?

B.
Example-of-Macro-for-changing---.xlsm
Microsoft Excel

Avatar of undefined
Last Comment
Bright01

8/22/2022 - Mon
Norie

What are the actual calculations you want?

Can you give some example?

I came up with the attached and it does change the formula.

However I'm not sure about the formulas, so I've just used what you posted.

It's also not clear what, if anything, you want to do with the Change value.

As far as I can see you can't use it as both a percent and $ amount without further calculation.
EE-Example-of-Macro-for-changing.xlsm
Norie

Right, had a think.

Do you want to convert 200% to $200 and vice versa?
Option Explicit

Sub Button1_Click()
Dim btn As Object


Set btn = Worksheets(1).Shapes(Application.Caller).OLEFormat.Object

    Select Case btn.Caption
        Case "%"
        
            With Range("E5")
                .Value = .Value * 100
                .NumberFormat = "\$###.00"
            End With
            
            Range("F5").Formula = "=D5-E5"
            btn.Caption = "$"
            
        Case "$"
        
                    With Range("E5")
                .Value = .Value / 100
                .NumberFormat = "%#.00"
            End With
            Range("F5").Formula = "=D5*E5"
            btn.Caption = "%"
        End Select
        
End Sub

Open in new window

Bright01

ASKER
Imnorie,

This is very good work.  Simple and right in line.  Now if you can take the button and when you change it from % to $ or $ to %, the conversion is such that the product and first number remain fixed/correct but the calculation and $ or % number readjusts.  So for example, if you have $500, 200%, $1000, and you change it from % to S, the numbers change, $500, $500, $1000.  In other words the 200% has been transformed into the appropriate $ equivalent.  One more example;  $600, -$400, $200, and you change it via the button from $ to %, then the middle $ amount changes to %; so $600, 33%, $200.

That's it.

Make sense?

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
Bright01

ASKER
Actually Imnorie, I've thrown you a curve.  In looking back over my conditions, I gave you the formula for changing the result and not re-establishing the $ or % variable.  So to be clear, if the button is set for %, then you enter the % and it produces the product.  When you hit the button again, it takes the product and changes it to the appropriate % or $ amount in the variable.

Very sorry for the confusion.

B.
Norie

That's kind of totally different.

You would need to calculate the Change amount/percent based on base an condition.
Norie

Ignore above, that was response to previous.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Norie

Try this, it sets the change value to 0 and changes the format.

I've also changed the formula for percent so that the percent is added to the base amount.

eg base = $500, percent =10%, results = $550
EE-Example-of-Macro-for-changing.xlsm
Bright01

ASKER
imnorie,

Thanks for the work!  Here's the math;

Base=$500, %=10%, results=$50
hit the button
Base=$500, $=-450, results=$50

another example;

Base=$500, $=-50, results=$450
hit the button
Base=$500, %=90%, results=$450

Make sense?

B.
ASKER CERTIFIED SOLUTION
Norie

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
Great work Imnorie!  Simple macros are the best......appreciate you "hanging with me" on this one as I changed the criteria at least once.

Much thanks,

B.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck