worksheet function match a named range in VBA

[juanbolas]

Hello,

I'm trying to write code that will check if the cell values have changed in a given range, and if so check if the value is in a list. If it´s not on the list it should open up a form to enter the new suppliers information.

I'm running into a problem trying to match the value entered in the cell to the list. the error i get is "Run-time error 1004: Unable to get the Match property of the WorksheetFunction class".

Code follows. Any help is appreciated.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.
If Not Intersect(Target, Range("Proveedores_Input")) Is Nothing Then
    If WorksheetFunction.Match(Target.Value, Sheets("Proveedores").Range("C5:C1000000"), 0) Then
'    If WorksheetFunction.Match(Target.Value, Sheets("Proveedores").Range("Proveedores_Lista"), 0) Then
        MsgBox Target.Value & " - Matched"
    End If
End If
End Sub

 

[Anthony47]

WorksheetFunction.Match will return that error if it doesn't find a match.
I use

myMatch = Application.Match(Target.Value, Sheets("Proveedores").Range("C5:C1000000"), 0)
If IsError(myMatch) Then
'   what to do if no match found
Else
'   what to do if there is a match
End If

myMatch has to be dimmed as variant

Beware that your code doesn't consider the possibility that more than one cell get changed

 

[rlv01]

Another option

Private Sub Worksheet_Change(ByVal Target As Range) 'Excel VBA with more cells in the range.
    Dim rngResult As Range
    
    If Not Intersect(Target, Range("Proveedores_Input")) Is Nothing And Target.Cells.Count = 1 Then
        Set rngResult = Sheets("Proveedores").Range("C5:C1000000").Find(What:=Target.Value, LookAt:=xlWhole)
        
        If Not rngResult Is Nothing Then
            MsgBox Target.Value & " - Matched"
        Else
            MsgBox Target.Value & " - NOT Matched"
        End If
    End If
End Sub

 

[juanbolas]

Thank you both!