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.

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

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

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 <SITCFa...@discussions.microsoft.com>
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.