Inputbox stripping decimal

[Luin29]

Hi everyone

I have the following code which is set to perform a unit conversion (multiple a cell value by a user input) on cells with specific text. The code works great except when the input number is less than zero; the problem being that the application.input drops everything after the decimal. Any idea how I can fix this?

Thanks

Public Sub Datatable_conversion()
Dim cell As Range, Rng As Range
Dim r, c
, Math As Long​
Dim i As Integer
Dim values() As Variant
Dim tempval As String, output As String, NDq As String


begin:
Set Rng = Application.InputBox(Prompt:="Please select the range to be converted." & Chr(10) & "(*Only select detections and non-detects*)", Title:="Range of Values", Type:=8)
NDq = Application.InputBox(Prompt:="Please enter the non-detect qualifier (i.e. ND<)", Title:="Non-detect Qualifier", Type:=2)
'Ask for range and set array
If Not Rng Is Nothing Then
    values = Rng
Else
    MsgBox ("Please enter a range.")
    GoTo begin
End If
'Ask for conversion factor
Math = Application.InputBox(Prompt:="Please enter the conversion factor that will be used to multiply the range by.", Title:="Conversion Factor", Type:=1)
If Math < 0 Then
    MsgBox ("Please enter a conversion factor.")
    GoTo begin
End If
answer = MsgBox("Convert detections as well?", vbQuestion + vbYesNo, "Convert Detections")
'Set number of rows
For r = LBound(values, 1) To UBound(values, 1)
    'Set number of columns
    For c = LBound(values, 2) To UBound(values, 2)
        output = vbNullString
        If Not IsEmpty(values(r, c)) And Not IsError(values(r, c)) Then
            If InStr(1, values(r, c), NDq) Then
                For i = 1 To Len(values(r, c))
                    tempval = Mid$(values(r, c), i, 1)
                    If tempval Like "[0-9]" Then
                        output = output & tempval
                    End If
                Next
                values(r, c) = "ND<" & (output * Math)
            If answer = vbYes Then: values(r, c) = values(r, c) / Math
            End If
        End If
    Next
Next
'Output array
Rng = values
End Sub

 

[Jeffrey Mahoney]

It looks like your variable "MATH" is set to LONG. LONG is an integer. DIM MATH as SINGLE or DOUBLE

 

[Kenneth Hobson]

Dim Math as Double

 

[Luin29]

Thank you Jeffrey and Kenneth. That is just what I needed.

On a similiar note, how would I keep trailing zeros when data is modified in an array. For my purpose, the trailing zero represents the accuracy of multiple test results and is crucial in final presentation of the data.

Thanks again.

 

[Kenneth Hobson]

Use Format() to format as needed.

 

[Jeffrey Mahoney]

When displaying results to the user in VBA, you will need to use:
Format(variable, "#,###.00000")
How many zeros you need after the decimal is up to you. Make sure your array is Dimmed as double if need that kind of precision.

 

[shg]

Math is a poor choice for a variable name -- it's the library where VBA math functions live (Math.Abs, Math.Log, Math.Sqr, ...).