• 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!
 

Marc L

Excel Ninja
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 …​
 

HurricaneBob

New Member
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
 

HurricaneBob

New Member
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.
 

Marc L

Excel Ninja
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 !​
 

Marc L

Excel Ninja
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 !​
 

HurricaneBob

New Member
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?
 

Marc L

Excel Ninja
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 !​
 

HurricaneBob

New Member
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:

Marc L

Excel Ninja
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 !
 
Top