Converting decimal values ​​to zeros

[shimaa01234]

I want to convert these numbers as in the example:

From	To
10.16	10.00
15.09	15.00
19.99	19.00

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

 

[pjmorris]

Hi Peter

I think I've worked it out!. Either method of identifying the range works, though the specialcells method identifies al cells within the starting range that meet the condition, thus you may end up with a set of discontinuous ranges. This can be handled by converting the returned range into a string, splitting it into an array and using each array element as the argument for your 'with range(adr)' statement. I failed to do anything clever with the Evaluate statement, and clearly I need to do more work in that area. The code below searches columns A:C reports which cells have constants and formulas and then converts the constants to integers.

S
Sub more_stuff()
    Dim cRng As Range
    Dim fRng As Range
    Dim rng As Range
    Dim adr As Variant    Dim n As Integer
    
    On Error Resume Next
    
    Set fRng = Range("A:C").SpecialCells(xlCellTypeFormulas)
    Set cRng = Range("A:C").SpecialCells(xlCellTypeConstants, xlNumbers)
    On Error GoTo 0
    
    If cRng Is Nothing And fRng Is Nothing Then
        MsgBox "Your data contains neither constants nor formulas"
        Exit Sub
    End If
        
    If Not cRng Is Nothing Then
        MsgBox "Constants are found at: " & cRng.Address
    End If
    If Not fRng Is Nothing Then
        MsgBox "functions are found at: " & fRng.Address
    End If
    
    adr = cRng.Address
    If Len(adr) - Len(Replace(adr, ",", "")) > 0 Then
        adr = Split(adr, ",")
    End If
    
    For n = 0 To UBound(adr)
        With Range(adr(n))
            .Value = Evaluate("IF(LEN(" & adr(n) & "),TRUNC(" & adr(n) & "),"""")")
            .NumberFormat = "0.000"
        End With
    Next
End Sub

Think I might have got a bit carried away with this one, but an interesting exercise nevertheless.

Regards