converting from general to decimal

**detlion1643** · 11-20-2009, 02:10 PM

I know this doesn't sound like a hard problem, but i just can't seem to find what will work. I have the numbers stored in column T as 7 7 7 7 5 5 5 5, etc.

I am trying to work on a macro that would basically change them to the decimal equivalent of the percentage.

Ex) 7 would become .07, 5 would become .05, etc.

I think the problem that i run into, is when trying to do anything on the same column as the numbers are stored into already. The values in column T must change, as the decimal equivalents need to stay in that column.

**DonkeyOte** · 11-20-2009, 02:13 PM

Don't understand - you want to alter the existing values or you don't ?

If you do, to illustrate enter 100 in a blank cell, copy it, highlight your values - > Edit -> Paste Special -> Operation: Divide

(the same process can of course be recorded...)

**detlion1643** · 11-20-2009, 02:22 PM

I understand how to change the value of 7 to .07 with mathematical equations for a different cell.

My question might not have been clear. If i have:

T1 - 4
T2 - 5
T3 - 7

I was looking to automate the process of changing the formatting for each value.
Right now, i manually type in .04, .05, .07.
It would save so much time if i could type in 4, 5, 7 and start a macro that would change them to .04, .05, .07.
The only catch, the numbers must stay in the T column.

After:
T1 - .04
T2 - .05
T3 - .07

**DonkeyOte** · 11-20-2009, 02:26 PM

The process outlined earlier will convert all values simultaneously by means of the Paste Special / Divide Operation (a not particularly well known feature).

If you want to alter the value upon entry (ie when T1 is committed) you're looking at using the Change event on the Sheet Object.

If you want to use VBA in terms of subroutines to be run as and when desired you can either automate the earlier Paste Special approach (ie use Macro Recorder) or you can use Evaluate, eg:

Please Login or Register  to view this content.

very much a case of horses for courses - use whichever approach you're most comfortable with.

**avveerkar** · 11-20-2009, 03:58 PM

A small macro can do the trick. To run the macro you could create a small button anywhere on the sheet. Using autoshape draw a small figure ( circle, oval or a rectangle ) on the sheet. This is going to be your button. Right click on this button, select "Assign Macro", select "New". And just create a macro like

Please Login or Register  to view this content.

And you are done. (I had used rectangle as a figure for button).

Now enter the values in column ( eg. T ). Select the cells you want to change and click on the button you have created. This macro simply divides the selected cells by 100.

**DonkeyOte** · 11-20-2009, 04:02 PM

avveerkar, I think you will find that would work only on single cell selections (it would also debug should someone inadvertently run the macro when the selection is not numeric).