Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting numbers
I have 8 sheets in my Excel File that has values in col E. I need to write a
macro, I'm thinking that is my best option, to divide each cell in column E of all the sheets by 100 so the numbers that display as. 748,029.00 should display as 7,480.29 2,666,406.00 should display as 26,664.06 206,633.00 should display as 2,066.33 I tried changing the format of the cells but that didn't work, can somebody help me with a macro to accomplish this. Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting numbers
the simple way of doing this is to put into column F the formula
=100*E1 and copy down the column the copy column F and use PasteSpecial Value and paste back into column e. You can then delte column F. If column f has data then use any empty column. "SITCFanTN" wrote: I have 8 sheets in my Excel File that has values in col E. I need to write a macro, I'm thinking that is my best option, to divide each cell in column E of all the sheets by 100 so the numbers that display as. 748,029.00 should display as 7,480.29 2,666,406.00 should display as 26,664.06 206,633.00 should display as 2,066.33 I tried changing the format of the cells but that didn't work, can somebody help me with a macro to accomplish this. Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting numbers
in cell F
=Macros(E1) =Macros(E2) =Macros(E3) Public Function Macros(value_ As Range) As Double Dim i, r As Double i = Replace(value_.Value, ",", "", , , vbTextCompare) r = Val(i) Macros = r / 100 End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
converting numbers
Unless you need to do this on a regular basis (in which case a macro
is a good idea), just do this: Put 100 into any empty cell. Copy that cell. Highlight the cells you need to convert, then use Paste Special (Edit | Paste Special, or right-click somewhere in the range and choose Paste Special from the popup dialog). Check Formulas in the Paste section of the dialog and Divide in the Operation section. Click OK and voila! Clear the cell holding the 100 value. Mark Lincoln On Dec 5, 9:11 am, SITCFanTN wrote: I have 8 sheets in my Excel File that has values in col E. I need to write a macro, I'm thinking that is my best option, to divide each cell in column E of all the sheets by 100 so the numbers that display as. 748,029.00 should display as 7,480.29 2,666,406.00 should display as 26,664.06 206,633.00 should display as 2,066.33 I tried changing the format of the cells but that didn't work, can somebody help me with a macro to accomplish this. Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a Column of Numbers Displayed as Text to Numbers | Excel Programming | |||
Converting positive numbers to negative numbers | Excel Worksheet Functions | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
Converting String numbers into real numbers | Excel Programming | |||
Converting String numbers into real numbers | Excel Programming |