Adding a decimal place

[Michelle]

I have some sheets with various number formats, I want to be able to
increase (and sometimes decrease) all of them by one or two decimal places.
So...

"0.0%" will become "0.00%"
"#,##0_);[Red](#,##0)" will become "#,##0.0_);[Red](#,##0.0)"
"£#,##0" will become "£#,##0.0"
etc...

Can I do this?

Is there a 'Number of DP' property of a cell?

Can I simply find out the number of DP there is in a format?

Many thanks

M

 

[JLGWhiz]

there is a FixedDecimal property, but that would apply to the entire
application, which means that all number formats would be whatever that
property is set to. I think that for your pruposes, NumberFormat is
probably the more appropriate property to toy with. It also seems that a
command button control would be useful so that the user could pick the time
and place to make the changes.

Private Sub CommandButton1_Click()
If 'some criteria Then
Columns(2).NumberFormat = "###,##0.000"
ElseIf 'another criteria Then
'another range NumberFormat = something
etc.
End If
End Sub

Or you could use an Input box to select the range

myRngToChange = Application.InputBox("Enter a Range", _
"Range to Format", Type:=8)
myRngRoChange.NumberFormat = "##,##0.0"
etc.

 

[Wouter HM]

Hi Michelle,

Using Excel 2003 I have created these macros:

Sub MoreDecimals()
Dim rngLoop As Range
Dim strFormat As String
Dim intPos As Integer
Dim strSepa As String

strSepa = "."

For Each rngLoop In ActiveSheet.UsedRange
If IsNumeric(rngLoop.Value) Then
rngLoop.Select
strFormat = rngLoop.NumberFormat
If strFormat <> "General" Then
intPos = InStr(1, strFormat, strSepa)
If intPos > 0 Then
strFormat = Replace(strFormat, ".", ".0")
Else
intPos = InStr(1, strFormat, "%")
If intPos > 0 Then
strFormat = Left(strFormat, intPos - 1) & ".0"
& Mid(strFormat, intPos)
intPos = InStr(intPos + 3, strFormat, "%")
If intPos > 0 And intPos < Len(strFormat) Then
strFormat = Left(strFormat, intPos - 1) &
".0" & Mid(strFormat, intPos)
End If
Else
If IsNumeric(strFormat) Then
If Len(strFormat) = 1 Then
strFormat = strFormat & ".0"
End If
Else
intPos = InStr(1, strFormat, ")")
strFormat = Replace(strFormat, "0_)",
"0.0_)")
strFormat = Replace(strFormat, "0)",
"0.0)")
End If
End If
End If
rngLoop.NumberFormat = strFormat

End If
End If
Next
End Sub

Sub LessDecimals()
Dim rngLoop As Range
Dim strFormat As String
Dim intPos As Integer
Dim strSepa As String

strSepa = "."

For Each rngLoop In ActiveSheet.UsedRange
If IsNumeric(rngLoop.Value) Then
rngLoop.Select
strFormat = rngLoop.NumberFormat
If strFormat <> "General" Then
intPos = InStr(1, strFormat, strSepa)
If intPos > 0 Then
strFormat = Replace(strFormat, ".0", ".")
strFormat = Replace(strFormat, ".%", "%")
strFormat = Replace(strFormat, "._", "_")
strFormat = Replace(strFormat, ".)", ")")
End If
If Right(strFormat, 1) = "." Then
strFormat = Left(strFormat, Len(strFormat) - 1)
End If
rngLoop.NumberFormat = strFormat

End If
End If
Next
End Sub

HTH,

Wouter