Excel WorksheetFunction.Vlookup is giving inconsistent results

Question

Community, I am working with very large Excel Workbooks (3200 lines of VBA, 127,536 KB). The goal is to determine if there are drawing number conflicts across/within corporate divisions. The Workbook contains worksheets(currently 5) that represent corporate divisions reserved drawing number lists.

Each Worksheet contains drawing numbers where the Worksheet can have between 50,000 to 200,000 rows and 2 to 5 columns that are formatted as text. I'm only using the first row at the moment.

The lists can contain drawing numbers like the following and can be up to 24 alpha/numeric/specialChar:

"38017"; "075242-DIV"; "00000006"; "20877137820"; "4110535 (223)"; "AS-107774-03"; "ADS168/580760 (223)"

I'm using a Dell Precision 7770, I9, 64 gig Ram, 6 tb storage. The excel takes 15-20 seconds to load. When I do a run it takes about 35min. to process 449384 rows of data populating 4 columns in each row with results after apply 1 of approx 250 rule patterns based on length, special characters, creator, suffix, prefix, construction patterns, etc.

I'm using a function "IsItemNumberStrInNumLogWks" to see if a drawing number is being used by a division. In this function I'm using "Application.WorksheetFunction.VLookup" and its giving me inconsistent results. There are many times where "Application.WorksheetFunction.VLookup" returns the "1004" when the value is definitely in the worksheet.

This issue is core to solving corporate drawing miss-alignment/conflict. Is there something I'm not applying correctly? Thanks for the help! Scott

Calling Code
Parameter values:
crntItemNumber = "22613900" :  gcNumLogNameTab = "Div1_Number_Log"
gcNumLogRangeDiv1 = "A2:B67113"


If IsItemNumberStrInNumLogWks(crntItemNumber, gcNumLogNameTab, gcNumLogRangeHTPG) Then .....

'=========================================================================================
' IsItemNumberStrInNumLogWks
' This checks to see if an item_number is in a divisions Number log worksheet
'===      Parameter Name              Object Type      Description
'           dwgNumStr                   String           drawing number Name; "1234-6789""
'           numLogTabName               String           the name of the worksheet tab; "Division_Number_Log"
'           strLogNumRange              String           the range of the Worksheet to be checked; "A2:B65878"
'
'=== Return:                            Boolean          If the dwg number pattern is found then return True
'

Public Function IsItemNumberStrInNumLogWks( _
  ByVal dwgNumStr As String, _
  ByVal numLogTabName As String. _
  ByVal strLogNumRange As String) As Boolean

    Dim numLogWks As Worksheet
    Dim numberLogRange As Range
    Dim numLogNumber As String: numLogNumber = "Not Found"
    'Dim dwgNumStr As String

    'dwgNumStr = "314545"  '314551 not found
         
    On Error GoTo SetAsFalse
    
    Set numLogWks = Worksheets(numLogTabName)
    Set numberLogRange = numLogWks.Range(strLogNumRange) '"A2:B65878"
    
    numLogNumber = Application.WorksheetFunction.VLookup(dwgNumStr, numberLogRange, 1, False)
    

SetAsFalse:
    If Err.Number = 1004 Then
       IsItemNumberStrInNumLogWks = False
      Else
       IsItemNumberStrInNumLogWks = True
    End If

End Function

Here is a data example as viewed notepad++ : All 3 values can't be found by VLookup.

"Div1_Number_Log" data as seen in Notepad++

[TAB] "Div1_Number_Log"
22613700    C/Z 6 BASE, PIPE DOWN
22613900    VENTURI SUPPORT RIGHT, ET-5H
22626600    ACCUMULATOR BASE

All Parts All Divisions" data as seen in Notepad++

[TAB] "All Parts All Divisions"
C/Z 6 BASE, PIPE DOWN   22613700    Component
VENTURI SUPPORT RIGHT, ET-5H    22613900    Component
ACCUMULATOR BASE    22626600    Component

If I create a new worksheet and use VLookup as a Formula the value is found as shown below: A3 = "22613900"

=IFERROR(VLOOKUP(A3,HTPG_Number_Log!$A$2:B$67113,1,FALSE),"Not Found") 

VLookup as a Formula. Same Data Reference

If I step through the code is get the error as shown below:

Stepping through the Code

Thanks for the help. Scott

Answer 1

Match (Lookup) Data

The Calling Procedure

Sub Test()

    If IsDrawingNumberFound(1234, "Sheet1") Then
        MsgBox "Found."
    Else
        MsgBox "Not!"
    End If

End Sub

The Function

Function IsDrawingNumberFound( _
    ByVal DrawingNumber As String, _
    ByVal LogSheetName As String) _
As Boolean
    
    Dim rg As Range
    
    With ThisWorkbook.Sheets(LogSheetName).Range("A1").CurrentRegion.Columns(1)
        Set rg = .Resize(.Rows.Count - 1).Offset(1)
    End With
    
    IsDrawingNumberFound = IsNumeric(Application.Match(DrawingNumber, rg, 0))

End Function

Answer 2

VBasic2008 Chris Neilsen Martin Brown FunThomas

Thanks for all the help, Guidance and time. I finally found the issue. The Excels I received from the division had the A column cells inconsistently populated with a hidden apostrophe. This is not detected by a Len() check and is not detected in NotePad++ and this is done by excel when a number is in a text field. I did find how to remediate it by Copying the columns and pasting the cells to a new column via the Values '123' option.

Another facet to this entire experience was the fact that Microsoft has added an Excel feature called "Automatic Conversion"; File\Options\Data\Automatic Data Conversion. If this is enabled Excel will Automatically convert numeric Text (data Type 2) to a Number (data Type 1) even if the Column Formatting is set to Text. This conversion happens when you paste into a cell or double clicking on a cell.

If you use VLookup from a formula Excel does not "seem" to look at the data Type, but the VBA version of VLookup does. So this is also something to watch out for.

Again thanks Scott