Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag 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
Avatar of Norie
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
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

Avatar of 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.
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.
That's kind of totally different.

You would need to calculate the Change amount/percent based on base an condition.
Ignore above, that was response to previous.
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
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
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.