Set decimal places by cell value

I am converting values from SAE to Metric and wish to retain the decimal places of the SAE value before converting. My code below converts nicely but doesnt maintain decimal places.

Option Explicit
Dim Sh1Range   'Sh1Range
Dim Sh1LastRow    'Sht1LastRow
Dim Sh1Cell    'Sh1Cell
Dim X
Dim iTimeStart As Double, iTimeEnd As Double, Y As Double

Sub Metric()
    With Sheets("Sheet1")
        'Loop thru Column E
        Sh1LastRow = .Cells(Rows.Count, "E").End(xlUp).Row
        Set Sh1Range = .Range("E1:J" & Sh1LastRow)
    End With

    For Each Sh1Cell In Sh1Range
        Sh1Cell.Activate
        If IsNumeric(Sh1Cell.Value) Then
            'get decimal place of cell value and set for converted value
            X = 25.4 * Sh1Cell.Value
            Sh1Cell.Value = X
        End If
    Next Sh1Cell
    Columns("E:J").Select
    Selection.Columns.AutoFit
    Range("E1").Select
End Sub

Any hints, tips and or examples are appreciated.

Dunno about SAE to metric, but I see you're converting inches to millimeters.

If the cells are formatted as General, they have no fixed number of decimals, and so will the results. If they are formatted to have some fixed number of decimals, the results will have the same number.

So ... what are you trying to do?

In my world, SAE is inch.
If the value of A1 is "1.000" then after I convert I want the new metric value to be "#.###" (3 decimals).
If the Value of A1 is "1.00" then after I convert I want the new metric value to be "#.##" (2 decimals).
And so on.

If they are formatted that way beforehand, your code won't change them.

However,

EDIT2:

Sub Metric()
    Dim r       As Range
    Dim cell    As Range
    Dim nDec    As Long

    With Sheets("Sheet1")
        Set r = .Range("E1", .Cells(.Rows.Count, "E").End(xlUp))
    End With

    For Each cell In r
        With cell
            If IsNumeric(.Value) Then
                nDec = InStr(.Text, ".")
                If nDec Then nDec = Len(.Text) - nDec
                .Value = 25.4 * .Value
                If nDec Then
                    .NumberFormat = "0." & String(nDec, "0")
                Else
                    .NumberFormat = "0"
                End If
            End If
        End With
    Next cell
End Sub

Originally Posted by shg

If they are formatted that way beforehand, your code won't change them.

This is what I thought as well, but it is not the case. My 2 decimal values become 3, 3 becomes 4, etc...

Edit: this isnt true. It seems random.

The cells are formatted as General.

General displays all the non-zero decimals within the space available. Try the revised code posted below.

Works great, Im just going to study this and figure out what you did!

Thank you.

Mark us up as Solved?

oops...