Changing a % to $ and $ to %

Bright01
Bright01 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
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
NorieAnalyst Assistant

Commented:
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

Author

Commented:
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.

Author

Commented:
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.
NorieAnalyst Assistant

Commented:
That's kind of totally different.

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

Commented:
Ignore above, that was response to previous.
NorieAnalyst Assistant

Commented:
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

Author

Commented:
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.
Analyst Assistant
Commented:
So the formula for dollars is actually D5+E5?
EE-Example-of-Macro-for-changing.xlsm

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial