VBA to change the decimal place based on some criteria

[ganeshkhatri]

Dear Excel Expertise,

I need a excel VBA to change the decimal place of all cell values based on some criteria.
For example if the number n>0.5 change the format to zero decimal, if the number is 0.5>n>0.05 keep it in one decimal, if the number is 0.05>n>0.005, keep the cell format in two decimal and etc.

i have been doing it manually as my management requirements and it’s tedious job for large data’s.

Please help me with this.

thanks
Ganesh

 

[kennypete]

How about this (in a Module to your Workbook):

Sub subSetNumberFormat()
    For Each cell In ActiveSheet.UsedRange.Cells
        If IsNumeric(cell) = False Then
            'do nothing
        ElseIf cell.Value > 0.5 Then
            cell.NumberFormat = "0"
        ElseIf cell.Value > 0.05 Then
            cell.NumberFormat = "0.0"
        ElseIf cell.Value > 0.005 Then
            cell.NumberFormat = "0.00"
        ElseIf cell.Value > 0.0005 Then
            cell.NumberFormat = "0.000"
        ElseIf cell.Value > 0.00005 Then
            cell.NumberFormat = "0.0000"
        ElseIf cell.Value > 0.000005 Then
            cell.NumberFormat = "0.00000"
        Else
            cell.NumberFormat = "0.000000"
        End If
   Next
End Sub

...which will work as a one-off when run on the Sheet, applying your required decimal places, ignoring and not formatting any non-numbers.

Optional: If you want it to be dynamic, i.e. as a cell changes, you could also have a Worksheet Sub too:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.UsedRange.Cells) Is Nothing Then
        If IsNumeric(Target.Value) = False Then
            'do nothing
        ElseIf Target.Value > 0.5 Then
            Target.NumberFormat = "0"
        ElseIf Target.Value > 0.05 Then
            Target.NumberFormat = "0.0"
        ElseIf Target.Value > 0.005 Then
            Target.NumberFormat = "0.00"
        ElseIf Target.Value > 0.0005 Then
            Target.NumberFormat = "0.000"
        ElseIf Target.Value > 0.00005 Then
            Target.NumberFormat = "0.0000"
        ElseIf Target.Value > 0.000005 Then
            Target.NumberFormat = "0.00000"
        Else
            Target.NumberFormat = "0.000000"
        End If
    End If
End Sub

 

[ganeshkhatri]

Thanks for your help. It worked it out except zero values cell. Can you fix the format for zero? I want to see "-" for zero values. Attached pics for your reference. It is now basically rounded off to "0.000000" format

Can you please change the code for above both vba code. thanks

 

[kennypete]

Add:

        ElseIf cell.Value = 0 Then
            cell.NumberFormat = "0"

after

        If IsNumeric(cell) = False Then
            'do nothing

...and you should be good.

Alternatively, you could apply no formatting at all for zero, just like the IsNumeric test, but if the 0.000000 has already been applied that'll not be an option. Depending on whether any other text may go into those cells too, cell.NumberFormat = "General" may be preferable.