• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shifting Number of Decimal Places

HurricaneBob

New Member
I'm trying to create a macro that can read the format of the current cell, determine the number of decimal places, and then add one more but I want it do be dynamic:

For example, if the current format is #,##0 , it will change the format to #,##0.0, and if the current format is #,##0.000 it will change to #,##0.0000

The tricky part is that I want to maintain the current cell format, so it would work with number, currency, percent, etc.

Appreciate any help!
 
As it depends on dynamic (often it means everything and nothing so better to avoid it) cells formats you use​
so without several samples it's difficult to help in another case than your unique sample. Obviously with an attachment …​
Easy way just adding a zero after the decimal separator via Replace or starting wih Split or …​
And do not forget to post the code you have started …​
 
Thank you! I got it to work with the following code:

Code:
Sub Add_Zeros()
    MyFormat = Selection.NumberFormat
    NewFormat = MyFormat & IIf(InStr(MyFormat, "."), "", ".") & "0"
    Selection.NumberFormat = NewFormat
End Sub
 
Thank you! I got it to work with the following code:

Code:
Sub Add_Zeros()
    MyFormat = Selection.NumberFormat
    NewFormat = MyFormat & IIf(InStr(MyFormat, "."), "", ".") & "0"
    Selection.NumberFormat = NewFormat
End Sub

That only works when there's a value for a positive format - if there's a negative format, it only modifies the negative format.
 
A positive / negative decimal only number format demonstration :​
Code:
Sub Demo0()
    D$ = Application.DecimalSeparator
    ActiveCell.NumberFormatLocal = Replace(ActiveCell.NumberFormatLocal, D & "0", D & "00")
End Sub
You may Like it !​
 
According to the initial post it could be the one :​
Code:
Sub Demo1()
    Const S = "#0"
    D$ = Application.DecimalSeparator
    ActiveCell.NumberFormatLocal = Replace(ActiveCell.NumberFormatLocal, S & IIf(InStr(ActiveCell.NumberFormatLocal, D), D, ""), S & D & "0")
End Sub
You could Like it !​
 
That's awesome! Is there a way to take it a step further and make it apply to a range of cells or would that require different logic?
 
The demonstration revamped :​
Code:
Sub Demo1r()
  Const S = "#0"
    Dim D$, Rg As Range
        D = Application.DecimalSeparator
    For Each Rg In Selection
        Rg.NumberFormatLocal = Replace(Rg.NumberFormatLocal, S & IIf(InStr(Rg.NumberFormatLocal, D), D, ""), S & D & "0")
    Next
End Sub
You should Like it !​
 
This is amazing! How did you learn to do all of this?

If I wanted to create another macro to go the other way (remove a 0), is there a function that is essentially the inverse of '& "0"'?
 
Last edited by a moderator:
Thanks to not quote all the previous post, that just clutter the thread.​
I learn from using the Macro Recorder and reading the VBA help (hit F1 key !) …​
You should have found yourself the remove way as the logic is easier …​
Anyway a VBA demonstration removing a decimal if the format has more than one :​
Code:
Sub Demo2()
    Dim R$, F$, Rg As Range
        R = "0" & Application.DecimalSeparator & "0":  F = R & "0"
    For Each Rg In Selection
        Rg.NumberFormatLocal = Replace(Rg.NumberFormatLocal, F, R)
    Next
End Sub
You may Like it !
 
Back
Top