Retain Trailing Zeroes when converting data

[BillTony]

I am accessing data from a different worksheet via VLOOKUP.

The data is brought over in the "General" format.

I must convert the data to the "Number" format for a set of calculations.

However, any method I have tried thus far drops any trailing zeroes.

I CANNOT force any arbitrary number of characters following the decimal point (0.00, 1.00000, etc.).

Following conversion, I must be able to present the data EXACTLY as the user entered it for quality control purposes.

For example, if a user entered a value as 17.6590000, that is what I MUST present.

'VLOOKUP in cell C3.
Range("C3").Formula = "=IF(VLOOKUP(RC[-2],'Filtered Data'!C[37]:C[61],25,FALSE)="""","""",VLOOKUP(RC[-2],'Filtered Data'!C[37]:C[61],25,FALSE))"
'CONDITIONAL AutoFill.
    If Last_Row_ColA > 3 Then
        Range("C3").AutoFill Destination:=Range("C3:C" & Last_Row_ColA)
    End If

'Remove all formulas from current worksheet.
Range("A3:ZZ" & Last_Row_ColA).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'The method below is UNSUCCESSFUL - basically using "Text to Columns."
'Select the RANGE.
    'Range("C3:C" & Last_Row_ColA).Select
Selection.TextToColumns Destination:=Range("K3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
       ':=Array(1, 1), TrailingMinusNumbers:=True

'The method below is also UNSUCCESSFUL - basically using a generic "self" value.
'Range("C3:C" & Last_Row_ColA).Select
 'With Selection
        '.Value = .Value
    'End With

 

[Joe4]

Since leading and trailing zeroes have no meaning to real numbers, they will be dropped. There is no way of getting around that for numeric entries (that is, numbers that are entered as numbers and not text). You cannot force trailing zeroes on real numbers.

If you want to retain the trailing zeroes, the numbers must be entered as text. As you know, that does not lend itself to be used in calculations.

You might need two copies of this data - one as Text to retain trailing zeroes for your display purposes, and one as numbers for your computations. You can "hide" the numerical copy, if you desire.

The only other option I can think of is to leave it as text, and convert that entries to numbers within your formula.
What is your exact formula that does not like it if it is a text entry?

 

[Rick Rothstein]

The decimal part of real numbers do not have trailing zeroes, either formatted numbers do (but that is only a display... the underlying real number does not have them) or text that looks like the number do. If this is a display thing, then you need to individually format the cell to display the decimal part as shown before converting the text representation to a real number. Assuming the cells in C3 on down are formatted as Text and contain the text representation of the numbers you want to convert to real number, then something like this should work (but remember, the real numbers resulting from this code will not have the trailing zeroes, only what is shown in the cell will)...

Sub Test()
  Dim X As Long, Cell As Range, Frmt As String
  For Each Cell In Range("C3", Cells(Rows.Count, "C").End(xlUp))
    Frmt = Cell.Text
    For X = 1 To Len(Frmt)
      If Mid(Frmt, X, 1) <> "." Then Mid(Frmt, X) = "0"
    Next
    Cell.NumberFormat = Frmt
    Cell.Value = Cell.Value
  Next
End Sub

 

[BillTony]

Thanks to both yourself and Joe4 for the insight!