the formatnumber() function does not display decimal numbers that have more than 3 decimal places

[gogi100]

i have next code

Dim z As Range
Dim inputstring As String
Dim brojdecimalnihmesta As Integer
Dim DecSep As String
Dim ThousendSep As String
Dim CurrentSelectionRng As String
Dim eIndex As Long
Dim eRowIndex As Long


CurrentSelectionRng = Selection.Address
On Error Resume Next

MsgBox "Pre nego sto unesete format kolone: broj, tekst, datum, procenat; izaberite jednu celiju u koloni, koja ce biti selektovana", vbOKOnly
inputstring = InputBox(Prompt:="Unesi format kolone('b' - broj, 't' - tekst, 'd' - datum, 'p' - procenat", Title:="Format kolone")


 If inputstring = "b" Then
     MsgBox "Format celije(a) ce biti broj", vbOKOnly
    brojdecimalnihmesta = InputBox(Prompt:="Unesi broj decimalnih mesta", Title:="Decimalna mesta")
    eIndex = Application.ActiveCell.Column
    eRowIndex = Application.ActiveSheet.Cells(Rows.count, eIndex).End(xlUp).Row
    Range(Cells(2, eIndex), Cells(eRowIndex, eIndex)).Select
    System = Application.UseSystemSeparators
     With Application
        .DecimalSeparator = ","
        .ThousandsSeparator = "."
        .UseSystemSeparators = False
    End With
      If System = True Then
            DecSep = Application.International(xlDecimalSeparator)
            ThousendSep = Application.International(xlThousandsSeparator) 'not used;for information only
      Else
            DecSep = Application.DecimalSeparator
            ThousendSep = Application.ThousandsSeparator 'not used;for information only
      End If
 
  
   For Each z In Selection
  
  If brojdecimalnihmesta = 0 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 1 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 2 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 3 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 4 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    ElseIf brojdecimalnihmesta = 5 Then
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)
    End If
  
   Next
    With Selection
  
    .HorizontalAlignment = xlRight
   ' .NumberFormat = "###,###,###,###0.00"
    
   End With
  
   Exit Sub
 End If

when I apply my macro each number for example 34563,9843 is displayed in the format 34,563.98 when I enter the number of decimal places 3 or 4 or 5. when I select the number of decimal places 1 the display in excel is 34,563.90. the display after the separator and decimals is fine with the menu, but why doesn't it display decimal places larger than 2.
I have the same problem with the percentage format.
I noticed when I entered the number of decimal places. This number actually rounds to that number of decimal places, and a always shows 2 decimal places

 

[Alex Blakenburg]

Is it your intention to have the number outputted as Text and not as a number ?
The numberformat function is intended to convert number to text. However when you are writing it to the spreadsheet it recognises it as a number and converting it back to a number with the default formatting, in your case 2 decimals.If you look in the formula bar you will see all the decimals.

If you want it to be a text output then format the cell as text first before writing to it.
Your whole If Else if seems unnecessary since all the formatting statements are the same. You really only need.

        z.NumberFormat = "@"
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)

 

[Alex Blakenburg]

If you did not intend to output it as text but did want it as a number then replace you whole If Else if statement with these lines

        Dim FmtNo As String
        FmtNo = "###,###,###,###0." & String(brojdecimalnihmesta, "0")
        z.NumberFormat = FmtNo
        z.Value = FormatNumber(z.Value, brojdecimalnihmesta, , , vbTrue)

Note to other helpers: If you want to test the code enter "b" in the first input box, which tells the code it is a number then in the second input box put a small integer telling the code the number of decimals you want in the output.
Warning: It also turns off Use System setting in File> Options > Advanced > Editing

 

[gogi100]

My intention is that i have the number outputted like number with the display all decimal places that number contains and format range also number with decimal places that i type in inputbox and thousand separator

 

[Alex Blakenburg]

Did you try my second option in Post #10 ? (If the code doesn't contain 4 lines refresh your screen because I edited it)

 

[gogi100]

Above code is ok.I have a similar code for the percentage, how to change this

 For Each z In Selection
 
  If brojdecimalnihmesta = 0 Then
        
        z.Value = CDbl(z.Value) / 100
        z.Value = Format(z.Value, "0%")
    ElseIf brojdecimalnihmesta = 1 Then
        z.Value = CDbl(z.Value) / 100
        z.Value = Format(z.Value, "0.0%")
    ElseIf brojdecimalnihmesta = 2 Then
        z.Value = CDbl(z.Value) / 100
        z.Value = Format(z.Value, "0.00%")
    ElseIf brojdecimalnihmesta = 3 Then
        z.Value = CDbl(z.Value) / 100
        z.Value = Format(z.Value, "0.000%")
    ElseIf brojdecimalnihmesta = 4 Then
        z.Value = CDbl(z.Value) / 100
        z.Value = Format(z.Value, "0.0000%")
    ElseIf brojdecimalnihmesta = 5 Then
        z.Value = CDbl(z.Value) / 100
        z.Value = Format(z.Value, "0.00000%")
    End If
   Next

 

[gogi100]

i found solution

 For Each z In Selection
  FmtNo = "###,###,###,###0." & String(brojdecimalnihmesta, "0") & "%"
        z.NumberFormat = FmtNo
        z.Value = CDbl(z.Value) / 100
        z.Value = FormatPercent(z.Value, , , , vbTrue)
 
   Next

 

[gogi100]

again i have a problem. when i apply my macro on another computer, for example formatting to number, the formatted column has cells, where it's stands error: this cell is fomatted like text...' i need that i have numbers with decimals places and thausand separator

 

[Alex Blakenburg]

Show me what it looks like before you start and what you want it to look like and the code you are running.

 

[gogi100]

on my computer all is ok, but another computer when start macro i received in range of cells the message 'this cell is formatted as number...' i want that this message does not displays and that value in cell is number, like on my computer. this code is

Sub FormattingColumn()
Dim z As Range
Dim inputstring As String
Dim brojdecimalnihmesta As Integer
Dim DecSep As String
Dim ThousendSep As String
Dim rng As Range
Dim DefaultRange As Range
'Dim CurrentSelectionRng As String
Dim System As Object
Dim eIndex As Long
Dim eRowIndex As Long
Dim FmtNo As String


 If TypeName(Selection) = "Range" Then
    Set DefaultRange = Selection
  Else
    Set DefaultRange = ActiveCell
  End If

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Opseg izbora konta", _
      Prompt:="Izaberi kolonu, gde su smestena konta, u formatu A1:A5", _
      Default:=DefaultRange.Address, _
      Type:=8)

'CurrentSelectionRng = Selection.Address
On Error Resume Next

MsgBox "Pre nego sto unesete format kolone: broj, tekst, datum, procenat; izaberite jednu celiju u koloni, koja ce biti selektovana", vbOKOnly
inputstring = InputBox(Prompt:="Unesi format kolone('b' - broj, 't' - tekst, 'd' - datum, 'p' - procenat", Title:="Format kolone")


 If inputstring = "b" Then
     MsgBox "Format celije(a) ce biti broj", vbOKOnly
    brojdecimalnihmesta = InputBox(Prompt:="Unesi broj decimalnih mesta", Title:="Decimalna mesta")
     System = Application.UseSystemSeparators
    
      If System = True Then
       With Application
        .DecimalSeparator = ","
        .ThousandsSeparator = "."
        .UseSystemSeparators = False
       End With
      End If
 
   rng.Select
   For Each rng In Selection
  
         FmtNo = "###,###,###,###0." & String(brojdecimalnihmesta, "0")
        rng.NumberFormat = FmtNo
        rng.Value = FormatNumber(rng.Value, brojdecimalnihmesta, , , vbTrue)
      
   Next
    With Selection
  
    .HorizontalAlignment = xlRight
      
   End With
  
   Exit Sub
 End If