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

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

Right, had a think.

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

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
```

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.

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

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.

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.

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

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.

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

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

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.

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.

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

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

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

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.