Using VBA Application.WorksheetFunction.Match if not found question

[gheyman]

I am trying to check to see if data already exist in a table by using the Application.WorksheetFunction.Match function.

And I am able to create a formula that works "If there is a Match".

Private Sub CommandButton2_Click()

Dim Find As Variant


'Sheet22.Range("B4").Value = Application.WorksheetFunction.Match(Sheet16.Range("B6").Value, Sheet22.Range("B6:B200"), 0)
Find = Application.WorksheetFunction.Match(Sheet16.Range("B6").Value, Sheet22.Range("B6:B200"), 0)

 If Find.Value > 1 Then
    
    Sheet16.Range("Buyer_List1").Copy
    Sheet22.Range("B5").End(xlDown).Offset(1).PasteSpecial
    Application.CutCopyMode = False
    
    Sheet22.Activate
    
    Else
    MsgBox "This Buyer ID is already included in the List"
    
End If


End Sub

how do i get this to work if it doesnt find a match?

Thanks

 

[Fluff]

How about

Find = Application.Match(Sheet16.Range("B6").Value, Sheet22.Range("B6:B200"), 0)

 If Not IsError(Find) Then

 

[dmt32]

Hi,
see if these changes to your code do what you want

Private Sub CommandButton2_Click()
    Dim BuyerID     As String
    Dim FindMatch   As Variant
    
    BuyerID = Sheet16.Range("B6").Value
    If Len(BuyerID) = 0 Then Exit Sub
    
    FindMatch = Application.Match(BuyerID, Sheet22.Range("B6:B200"), 0)
    
    'no match
     If IsError(FindMatch.Value) Then
        
        Sheet16.Range("Buyer_List1").Copy
        Sheet22.Range("B5").End(xlDown).Offset(1).PasteSpecial
        Application.CutCopyMode = False
        
    Else
        'match
        MsgBox BuyerID & Chr(10) & "This Buyer ID is already included in the List", 48, "BuyerID Exists"
        
    End If


End Sub

Dave

 

[gheyman]

Thank you both but I get a Run Time '424' Object required error. Its identifying line: "If IsError(FindMatch.Value) Then" if I use Dave's solution

and if I use Fluffs and the buyer ID doesnt already exist I get a run time 1004 unable to get the Match property of the WorksheetFunction class.

Not sure what I am doing wrong I thought this would be easy with the Match function. I would get a value if there was a match and not one if it didnt. making my head spin. lol

 

[gheyman]

correction and apologies: if mistyped Fuff's solution. it worked.

Thank you both

 

[dmt32]

Thank you both but I get a Run Time '424' Object required error. Its identifying line: "If IsError(FindMatch.Value) Then" if I use Dave's solution

my bad should have been

If IsError(FindMatch) Then

Dave

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[flaxman]

works for me

On Error Resume Next
FindMatch = Application.Match(BuyerID, Sheet22.Range("B6:B200"), 0)
On Error GoTo 0