number format?

[Steve]

Morning all.
I'm going through multiple worksheets, and trying to make sure that the
values that are in the cells are what's actually showing.
I.e., fo rthe most part I set my numeric format to show two decimal places.
If however there are 3 decimals in the number, I need to show that. Or, if
there are 4 decimal places, I'll need to show them.
It's getting rather tedious going through each and every worksheet and doing
this one row at a time, manually.

As such, I'd like to do this with a macro.
So, my question is:

What code would I use to check to see the amount of actual decimal places
there are in a given number, and then set the number format to show the
decimal places that actually exist, with never less than 2 decimal places?

At this point, what's rolling through my mind is:

If activecell.value = 3 decimal place number then
set activecell.format = format("0.000")

else if activecell.value = 4 decimal place number then
set activecell.format = format("0.0000)

else
activecell.value = 2 decimal place number
set activecell.format = (format("0.00")

How would I perform this task?

 

[joel]

You can't because some number like 1/3 will repeat and not stop at 2 or 3
decimal places.

 

[Steve]

Ok,
However, in my case, I won't be using fractional numbers for this-- at all.
Nor would I use e^, Pi, etc.....
All I'll be using will be 0 to 4 decimal place numbers.

So, what can I use for this?

Thank you for your quick response.

 

[Nigel]

If these values have been manually entered then you could test for number of
DPs, but if using formula the results will be uncertain due to the floating
point calculation. Try using something like Len(Trim(Cell - Int(Cell)))-2
to obtain the length of the fractional part. The -2 remove the count for
leading zero and DP
--

Regards,
Nigel
(e-mail address removed)

 

[joel]

Will this work?

For Each cell In UsedRange
If IsNumeric(cell) Then

Numplaces = Len(cell.Value) - InStr(cell.Text, ".")
cell.NumberFormat = "0." & String(Numplaces, "0")

End If
Next cell