Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a decimal place
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a decimal place
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a decimal place
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to place a decimal after 3rd digit | Excel Discussion (Misc queries) | |||
Count first decimal place only | Excel Discussion (Misc queries) | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
changing to two place decimal | Excel Worksheet Functions |