Prev: Speciefie table and not cells to sum
Next: How to run a macro if I don't know the name in advance
From: Michelle on 7 May 2010 12:01
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

From: JLGWhiz on 7 May 2010 13:48
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.




"Michelle" <mh_londonNOJUNK(a)hotmail.com> wrote in message
news:4ECF4EEF-0CE2-4E4C-8DA2-D575035A5425(a)microsoft.com...
>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


From: Wouter HM on 8 May 2010 09:32
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
 | 
Pages: 1
Prev: Speciefie table and not cells to sum
Next: How to run a macro if I don't know the name in advance