Make XMatch return "true" or "False"

[Brian Feth]

Windows 11, Excel 2021: I'm trying to get XMatch to return "true" or "false". This compares a master list of vendors (Costco, Disney, Amazon, etc.) to a large column of vendors in random order to find the vendors that are missing from the master list and add them to the list. I know it's possible because I've done it before but can't remember how, and I can't find an example. Any help that can be given is alway appreciated. Please ask if clarification is needed.

Sub XMatch_ToAddVendors()

Dim MatchResult      As Variant
'Dim MatchResult      As Boolean
Dim Vendor1_Top       As Long
Dim Vendor2_Bot       As Long
Dim NumVendor1_Rows   As Long
Dim NumVendor2_Rows   As Long
Dim i                 As Integer
Dim x                 As Integer
Dim Vendor1_Val       As String

Range("H1000").Select
Selection.End(xlUp).Select
Vendor1_Top = Selection.Row
NumVendor1_Rows = Range("H1000:H" & Vendor1_Top).Rows.Count

Range("H1010").Select
Selection.End(xlDown).Select
Vendor2_Bot = Selection.Row
NumVendor2_Rows = Range("H1010:H" & Vendor2_Bot).Rows.Count

For x = 0 To NumVendor2_Rows
For i = 0 To NumVendor1_Rows

    Vendor1_Val = Range("H" & Vendor1_Top + i).Value 'Just to keep track or the value being examined
   
  MatchResult = Application.XMatch(Range("H" & Vendor1_Top + i), Range("H1010:H" & Vendor2_Bot + x), 0, 1)
  
    If MatchResult = True Then
        i = i + 1
    ElseIf MatchResult = False Then
        Range("H1010").Select
        Selection.End(xlDown).Select
        Vendor2_Bot = Selection.Offset(1, 0).Row
        Range("H" & Vendor2_Bot) = Vendor1_Val
        x = x + 1
    End If
   
        Next i
    Next x
     
End Sub

 

[Brian Feth]

I figured it out. The answer is adding "Not IsError" to the front of the equation e.g.:

MatchResult = Not IsError(Application.XMatch(Range("H" & Vendor1_Top + i), Range("H1010:H" & Vendor2_Bot + x), 0, 1))

Don't know why that works. I'd be interested in having it expained to me.

 

[Alex Blakenburg]

The XMatch function will return either a number or #N/A.
IsError(number) = False
IsError(#N/A) = True
You want the opposite to that so putting NOT before the Iserror reverses the logic.
Note: This only works because you used Application.XMatch. If you had used WorksheetFunction.XMatch you would have had to handle a VBA error, IsError would not have worked

 

[Brian Feth]

Thank you for taking thr time to answer my question. So much nuance, so may opportunities to walk off a cliff. Thanks again.

 

[Alex Blakenburg]

You're welcome.

 

[Peter_SSs]

Excel 2021

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.