Worksheet_Change works every other time

[ShawnExcel]

I am currently attempting to sort a large table based on a dropdown on another page using Worksheet_change. I have attached an example. This works about 50% of the time but it errors out half the time. Does anyone know why?

Error:
Unable to get the Match Property of the WorksheetFunction Class

Here is the code:

Private Sub Worksheet_Change(ByVal Facility_Type As Range)

    If Facility_Type.Address = ActiveWorkbook.Sheets(1).Range("C4").Address Then
 
    FacilityRange = Worksheets("Sheet1").Range("A2:H2").Value
 
    MsgBox (Facility_Type)
     
MatchedCell = Application.WorksheetFunction.Match(Facility_Type, FacilityRange)

MsgBox (MatchedCell)

Else

MsgBox ("Range Not Found")

End If

End Sub

My GOAL is to be able to select a dropdown option (i.e. BEEF) and have the table on the next page filter so only "YES" is shown for Beef (i.e. filter out any "No" rows for the selected food).

Can anyone help with this?

Thank you so much!

 

[Luke M]

Hi Shawn,

It looks like the wrong attachment got uploaded? I remember helping before on the trivia type question, and this doesn't seem to correspond with the text in your post.

 

[ShawnExcel]

Great point! I just fixed it. That's what I get for not cleaning up my desktop. Thanks!

 

[ShawnExcel]

Now that I fixed it - does anyone have any suggestions?

 

[p45cal]

are you not looking for an exact match (the list isn't sorted):
MatchedCell = Application.WorksheetFunction.Match(Facility_Type, FacilityRange, 0)

 

[Luke M]

Try this:

Private Sub Worksheet_Change(ByVal Facility_Type As Range)
'Check if cell is of interest
If Intersect(Facility_Type, Me.Range("C4")) Is Nothing Then Exit Sub

Call FilterTable(Facility_Type.Value)

End Sub

Sub FilterTable(fWord As String)
'Looks for fWord in the header row of a table, and filters
'that column for the FindThis string

Dim tb As ListObject
Dim fCol As Long
Const FindThis As String = "Yes"

'Where is the table?
Set tb = Worksheets("Sheet1").ListObjects(1)

'Initialize our value
fCol = 0
'In case of the MATCH failing, force code to keep moving forward
On Error Resume Next
fCol = WorksheetFunction.Match(fWord, tb.HeaderRowRange, 0)
On Error Resume Next

'if we failed to find column
If fCol = 0 Then
   
    MsgBox "That value was not found"
    Exit Sub
End If

'Otherwise, unfilter everything, then filter the correct column
With tb.Range
    .AutoFilter
    .AutoFilter field:=fCol, Criteria1:="=" & FindThis
End With

End Sub