Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: imputbox change decimal value dot to comma

  1. #1
    New Member
    Join Date
    Aug 2019
    Location
    Portugal
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default imputbox change decimal value dot to comma

    Hi, I have been searching without success for the following:

    I have a imputbox to receive euros, if the user wants to insert for example the number 55.20, afterwords I will use that number to do another calculation, but is giving me an error because its inserted with a dot as a decimal separator instead of a comma, if I use a dot separator gives me the following error:

    Run-time error'13': Type mismatch



    Can I use something like: resposta = Replace(Textnumber.Value, ".", ",") to force the change from dot to comma to avoid the system error?



    Example of the code:

    Code:
        Dim resposta As Variant
        
        
        resposta = InputBox("Valor da Nota de Débito")
        
        
        Dim ValorQuota As Variant
        ValorQuota = WorksheetFunction.Sum(Range("H4:H5"))
    
        
        Dim Resultado As Variant
        
            
        Resultado = resposta / ValorQuota
        
        MsgBox Resultado
    Thank you.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    How about
    Code:
        If InStr(1, resposta, ".") > 0 Then resposta = Replace(resposta, ".", ",")
    Last edited by Fluff; Aug 23rd, 2019 at 10:02 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Location
    Portugal
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    It works but then is resulting in another problem.

    after the calculation is being stored as string in "ValoND" and I was trying to convert it to number but keeping de decimal part, for example the number 55,70 in the inputbox is returned as 56,00 by ValorND in cell "O5", do you have any ideas to keep a decimal number in the same way 55,70? Thank you.
    I had changed some names of the variables for better understanding, sorry.

    Example:

    Code:
        'Imput box para pedir valor de Nota de Débito
        
        Dim ValorND As String
        Dim ValorQuota As String
        Dim Resultado As String
        
        ValorND = InputBox("Valor da Nota de Débito")
            
        'Se for inserido um "ponto" transforma em virgula
        If InStr(1, ValorND, ".") > 0 Then ValorND = Replace(ValorND, ".", ",")
        
        ValorND = CInt(ValorND)
        ValorND = FormatNumber(ValorND, 2)
    
        
        'Exit Sub
       
       
        'Soma a quota com o fundo de reserva para achar o valor total da quota da fração em questão
        
        ValorQuota = WorksheetFunction.Sum(Range("H4:H5"))
    
        
     
        
        'Faz o teste para ver se é numero inteiro ou com casas decimais
        
        Resultado = ValorND / ValorQuota
        
        MsgBox Resultado
        
        Range("O5").Value = ValorND
        
        Exit Sub

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    Change this
    Code:
    ValorND = Val(ValorND)
    Otherwise you're converting it to an integer
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Location
    Portugal
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    Quote Originally Posted by Fluff View Post
    Change this
    Code:
    ValorND = Val(ValorND)
    Otherwise you're converting it to an integer
    Even with that change,

    If I put in the inputbox for example 55.70 the output from the variable ValorND in the cell "O5" is 55.00 and I wanted to be 55.70

    Code:
    ValorND = Val(ValorND)
    
    'I added this line to make sure the decimal part was there but the value continues rounded to 55 
    ValorND = FormatNumber(ValorND, 2)
    I cannot understand what is happening to de decimal part, simply disappears .

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    Is your system set to use a . or a , for decimals?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Aug 2019
    Location
    Portugal
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    The system uses , for decimals.

    Sorry for all the trouble, all that I wanted was an simple input box to store a value in euros, that most of the times have decimal part, keep it in a variable, do some calculation and paste it in a cell as a number with that same decimal part.
    Last edited by Blue_Wings; Aug 23rd, 2019 at 12:09 PM.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    Ok, that sounds as though VBA requires a . for decimals regardless of local settings.
    Try
    Code:
        'Imput box para pedir valor de Nota de Débito
        
        Dim ValorND As String
        Dim ValorQuota As String
        Dim Resultado As String
        
        ValorND = InputBox("Valor da Nota de Débito")
            
        'Se for inserido um "ponto" transforma em virgula
       
        'Soma a quota com o fundo de reserva para achar o valor total da quota da fração em questão
        
        ValorQuota = WorksheetFunction.Sum(Range("H4:H5"))
    
        
     
        
        'Faz o teste para ver se é numero inteiro ou com casas decimais
        
        Resultado = ValorND / ValorQuota
        
        MsgBox Resultado
        If InStr(1, ValorND, ".") > 0 Then ValorND = Replace(ValorND, ".", ",")
    
        Range("O5").Value = ValorND
        
        Exit Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Aug 2019
    Location
    Portugal
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    This last option keeps the decimal part like it should be but, for example in the inputbox 55.7 it stores in the cell 55,7 but as text and not as a number.

    I need it to be a number in the spreadsheet to be able to sum
    Last edited by Blue_Wings; Aug 23rd, 2019 at 12:22 PM.

  10. #10
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,057
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: imputbox change decimal value dot to comma

    Try this:
    Format cell O5 as Number with 2 decimal places, then run this code:

    Code:
    Sub a1107817a()
       'Imput box para pedir valor de Nota de Débito
        
        Dim ValorND
        Dim ValorQuota
        Dim Resultado
        
        ValorND = InputBox("Valor da Nota de Débito")
            
        'Se for inserido um "ponto" transforma em virgula
       
        'Soma a quota com o fundo de reserva para achar o valor total da quota da fração em questão
        If InStr(1, ValorND, ",") > 0 Then ValorND = Replace(ValorND, ",", ".")
        ValorND = Val(ValorND)
        ValorQuota = WorksheetFunction.Sum(Range("H4:H5"))
    
        
     
        
        'Faz o teste para ver se é numero inteiro ou com casas decimais
        
        Resultado = ValorND / ValorQuota
        
    '    MsgBox Resultado
    
        Range("O5").Value = ValorND
        
    
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •