CODEWITHSUNDEEP
vbaexcelexcel-formulafind
Moosli
Moosli

Reputation: 3285

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

I have en Excel List with ID's and Data(About 8000 Rows and more). The ID's can be only digits oder Digits with charaters. Like "2222222222222223" or "222222222222222A" (the Cells with the ID are formatted as Text)

If i try to find the Entry Address with a ID, it will insert a now row, because it will not find them. What means that at the End i have Doubled all Entries.


So my Question is, how many characters does the find method compare? 

With objSheet
     strAdresseExcel = .Cells.Find(What:=str, After:=.Cells(1, 1), _ 
     LookAt:=Excel.XlLookAt.xlWhole, MatchCase:=True, SearchFormat:=False).Address
    End with

So i created a dummy List with 8000 Rows where all the ID's had the Number "2222222222222222" and i found something out.

That Excel compares only the first 15 characters if there are only Digits in the String but i works fine if there is a Letter in the ID! You can see the issue in the Image below

Information to the Image:
Row A:
i worked with Conditional Formatting.
(On the Home tab, click Conditional Formatting, Highlight Cells Rules, Duplicate Values)
Works only when the ID has Digits or there is a difference in the first 15 Characters.

Row B:
I worked with a Formula. (=IF(COUNTIF(A:A;A2)>1;"Double";""))
How you can see, the same Problem happens here as well.

enter image description here

Upvotes: 10

Views: 492

Answers (2)

ttaaoossuu
ttaaoossuu

Reputation: 7894

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.

enter image description here

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

Upvotes: 1

Ryan Wildry
Ryan Wildry

Reputation: 5677

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

Upvotes: 1

Related Questions