Why does the .Find() method in Excel VBA appear to only check the first 15 characters?

This seems to work for me, unless I'm missing something, which is entirely possible :)

It returns the first address where the cell matched and prints out the lengths of the strings searched. I have the data set setup in Sheet1 just as shown, with the numeric data being stored as Text (I added an apostrophe at the start of each value)

 Sub testSO()    Dim objSheet   As Worksheet: Set objSheet = ThisWorkbook.Sheets("Sheet1")    Dim strToFind  As String: strToFind = "22222222222222222222222"    Dim strAddress As String    strAddress = objSheet.Cells.Find(strToFind, objSheet.Cells(1, 1), _    LookAt:=Excel.XlLookAt.xlWhole, MatchCase:=True, SearchFormat:=False).Address    'Print out findings    Debug.Print strAddress, Len(strToFind), Len(objSheet.Range(strAddress))End Sub

Output is:

$A$5 23 23

As it was pointed out, COUNTIF() will parse values as numeric where possible before comparing.

Although Excel can display 30 decimal places, its precision for a specified number is confined to 15 significant figures, and calculations may have an accuracy that is even less due to three issues: round off, truncation, and binary storage.

As you can see in the example, the 16-digit values that can be converted to integers are compared by their first 15 significant digits, so it appears as if there are 10 occurrences of each in the column, while strings having letters are correctly evaluated by COUNTIF() as unique within the column.

I could not reproduce this issue with VBA .Find() but conditional formatting by duplicates shows the same behavior as COUNTIF().

More on the issue: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel