imputbox change decimal value dot to comma

[Blue_Wings]

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:

    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.

 

[Fluff]

What happens if you put the value straight into the cell

ValorND = InputBox("Valor da Nota de Débito")
Range("O5").Value = ValorND

 

[Blue_Wings]

As simple as that!
It Works!

The confusion was because I wanted to use that same variable "ValorND" to do a calculation while inside the VBA with another variable "ValorND / ValorQuota" , and that was causing error Run-time error'13': Type mismatch, so I decided to add an auxiliary variable "ValorNDAux"to deal with that, and now is working.

thanks you.

    'Imput box para pedir valor de Nota de Débito
    
    Dim ValorND As String
    Dim ValorNDAux As String
    
    Dim ValorQuota As String
    Dim Resultado As String
    
    ValorND = InputBox("Valor da Nota de Débito")
    
    'Valor auxiliar para ser convertido de . para , sem causar erro no "Resultado"
    ValorNDAux = ValorND
    
    'Se for inserido um "ponto" transforma em virgula
    If InStr(1, ValorNDAux, ".") > 0 Then ValorNDAux = Replace(ValorNDAux, ".", ",")
    
  
   
    '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 = ValorNDAux / ValorQuota
     
    Range("O5").Value = ValorND

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[Akuini]

As simple as that!
It Works!

Hi, Blue_Wings
Just courious, if you enter 5,88 (with comma) then what happen?
My system use comma as decimal separator too, & the result is (using your last code) in cell O5 is string.
Did you try my code in post #10 ?

 

[Blue_Wings]

Hi, Blue_Wings
Just courious, if you enter 5,88 (with comma) then what happen?
My system use comma as decimal separator too, & the result is (using your last code) in cell O5 is string.
Did you try my code in post #10 ?

I missed your #10 reply and didn´t tried it.

But answering your question, if I entered 5,88 with a comma everything works fine, it was just to avoid error, since most people uses dot from the numeric pad on the keyboard, like this I can use both ways and its always converted to comma so that the system on the spreadsheet can do calculations and doesn´t return any error.

 

[Akuini]

if I entered 5,88 with a comma everything works fine

You mean it inserts number in cell 05? not string?

 

[Blue_Wings]

You mean it inserts number in cell 05? not string?

Yes, correct.