imputbox change decimal value dot to comma

Blue_Wings

New Member
Joined
Aug 23, 2019
Messages
11
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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,621
Office Version
365
Platform
Windows
How about
Code:
    If InStr(1, resposta, ".") > 0 Then resposta = Replace(resposta, ".", ",")
 
Last edited:

Blue_Wings

New Member
Joined
Aug 23, 2019
Messages
11
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,621
Office Version
365
Platform
Windows
Change this
Code:
ValorND = [COLOR=#ff0000]Val[/COLOR](ValorND)
Otherwise you're converting it to an integer
 

Blue_Wings

New Member
Joined
Aug 23, 2019
Messages
11
Change this
Code:
ValorND = [COLOR=#ff0000]Val[/COLOR](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

Rich (BB 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 .
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,621
Office Version
365
Platform
Windows
Is your system set to use a . or a , for decimals?
 

Blue_Wings

New Member
Joined
Aug 23, 2019
Messages
11
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,621
Office Version
365
Platform
Windows
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
 

Blue_Wings

New Member
Joined
Aug 23, 2019
Messages
11
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:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,559
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,089,474
Messages
5,408,454
Members
403,207
Latest member
JFoley182

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top