Bright01

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

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

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.

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.

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.

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.

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

EE-Example-of-Macro-for-changing.xlsm

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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

Much thanks,

B.

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