Change event to Convert (calculate) user input if user types % sign

How can I convert a user's input from the input value to something else IF the user typed a value and % sign? In other words, user types 10% in A1, which i capture in the change event. Since user typed % sign, I want to change the original input (10%) to the result of 10% * A2. The problem is for this to work the cell numberFormat MUST be accounting and two decimals. I want the cell format to be 0 instead of 0.00 though.

Below is what I have that works only if the format per below (accounting). It basically takes the user's input and recalculates it as a percent of the amount in the adjacent column..if the user types 10%. Keep in mind the target numberFormat must remain as 0 (and not 0% nor 0.00, which is what the below code must do to work). Any tips are greatly appreciated!

To rephrase:
1. Target must be formatted as a number, eg., 0.
2. If user inputs target value with a % (eg, 10%), recalculate input as 10% * adjacent column. Retain original format as 0.
3. If user inputs a number target value (eg 10), no action to take. Retain format as 0.

Please Login or Register  to view this content.

Hi Soundneedle...

Fistly please read the forum rules, I refer particularly to rule 3 regarding using code tags when posting code.

Now your example description starts by talking about A1 and A2, and A1 changes getting multiplied by A2, yet your offset suggests you are not moving forward but backward. If you change A1, then an offset of (0,-1) is impossible since there is no column before A.
Additionally you later mention the "adjacent column" yet your example (A1/A2) is looking at the next row, not column.

Can you provide a clearer explanation?

Originally Posted by Arkadi

Hi Soundneedle...

Fistly please read the forum rules, I refer particularly to rule 3 regarding using code tags when posting code.

Now your example description starts by talking about A1 and A2, and A1 changes getting multiplied by A2, yet your offset suggests you are not moving forward but backward. If you change A1, then an offset of (0,-1) is impossible since there is no column before A.
Additionally you later mention the "adjacent column" yet your example (A1/A2) is looking at the next row, not column.

Can you provide a clearer explanation?

Basically, the point is this code only works if the target is specifically formatted as "accounting". My question is why... What's so special about this format? I've tried similar number formats where it has two decimals, for example. However, when user types 20% into the target excel immediately converts to 0.2. If the format is accounting excel keeps the % character which allows my code to work properly.

The code is dumbed down for simplicity. Takes the target value and determines if user typed the number input as a percentage. If so, do a simple calc using target and adjacent cell. No issues with the calc part... Just the initial part where it determines if a percent (sign) was in the user input.

What if you just format the cells as text? you can still make the string you later put back in r1 a string that is the numerical value you want, formatted the way you want it.