CODEWITHSUNDEEP
vbaexcel
konstantintre
konstantintre

Reputation: 37

Format with maximum "after decimal places" length

I have a range containing numbers with different numbers of decimal places.

I need to write a code which formats the whole range such that the number of decimal places is equal to the maximum number of decimal places among the values in the range.

Example:

2.45
3.38574
6.1

should become:

2.45000
3.38574
6.10000

How do I do this?

Upvotes: 0

Views: 1143

Answers (2)

ggle
ggle

Reputation: 466

Try this:

Sub decPlac()

places = 0

For Each sell1 In Sheets("sheet1").Range("A1:A10")
    dots = Len(CStr(sell1)) - InStr(1, CStr(sell1), ".")
    If dots > places Then
        places = dots
    End If
Next

For Each sell2 In Sheets("sheet1").Range("A1:A10")
    sell2.NumberFormat = ("0." & WorksheetFunction.Rept("0", places))
Next

End Sub

Just change ("A1:A10") to whatever your range is in both loops

Upvotes: 0

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

I suggest this high-level approach:

  1. Convert the numbers to strings.
  2. In each string, find the decimal point character ..
  3. Count the characters after this decimal point; this will give you the number of decimal places in each number.
  4. Take the maximum of those.
  5. Based on this maximum, make a number format code string (e.g. for 4 decimal places you would need something like #.0000), and apply it to your range using the NumberFormat property.

Try it. If you get stuck at a specific step, ask a new question detailing what you have tried for that specific step and how exactly it does not give the expected results.

Upvotes: 1

Related Questions