Macro partially not working

[Nite0wls]

Hi All,

I have the following macro:

Sub Tidy2()
    Dim Cell As Range
    Set TelRange = ActiveWorkbook.ActiveSheet.Columns("A").SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues)
    For Each Cell In TelRange
        With Cell
            If Left(.Value, 6) = 416712 Then
                .Value = 18882714615#
            End If
        End With
    Next Cell
End Sub

And I am going through a long list were it finds several matches.
However I noticed that it replaces the value 416712888271461 correctly but when it comes to a cell with the value 4167128882714610 (so one digit longer being the zero at the end) it does not replace the cell content?

Can someone please explain me what's going on here and how to resolve this?

 

[iliauk]

Try:

.Formula = 18882714615#

(or .Value2)

I think .Value is giving you 4.16.. x 10^...

 

[Nite0wls]

Thanks for the feedback,

I now use: If Left(.Formula, 15) = 416712888271461# Then and that finds a match correctly.

Many thanks