VBA autofilter based on multiple cell values, using array including a wildcard does not work

[Marmot3292]

Hi All - I am trying to build a screening tool using VBA autofilters. Below is a screenshot of my data and code. The idea is the user is able to populate the cells in B2:C5 using dropdowns or a numerical value, then run some code to filter the table to produce results. If a field in C2:C5 is left blank, the autofilter will be skipped over for that column, i.e. all rows will be shown. So below I have only applied a filter to Sector for Consumer Discretionary, so all Consumer Discretionary companies are shown.

Sometimes data is not available for a field (data is pulled from Bloomberg) and so we get an error #N/A N/A as highlighted below. I wanted to add logic into my code so that in addition to my parameters set in B2:C5, it would also show data containing a "#" which I have attempted to do using a wildcard below...

My code is below. Without the items shown in bold, this macro runs fine but I want it to include the # data fields as well. When I include the bits highlighted in bold/red, I get "Compile Error: Expected: expression".

Can anyone help?

Sub MultiCriteriaFilterIncErrors()

With Worksheets("FilterData2")

    'Clear existing filters from table
    If Range("A7").AutoFilter Then
    Range("A7").AutoFilter
    End If
         
    'If no parameter entered, do not apply filter to column
    If Range("c2") = "" Then
    .Range("A7").AutoFilter Field:=2
    'If parameter entered, apply filter
    Else
    .Range("A7").AutoFilter Field:=2, Operator:=xlFilterValues, _
    Criteria1:=(Range("b2").Value & .Range("c2").Value)
    End If
   
    'Market Cap Filter
    If Range("c3") = "" Then
    .Range("A7").AutoFilter Field:=3
    Else
    .Range("A7").AutoFilter Field:=3, Operator:=xlFilterValues, _
    Criteria1:=(Range("b3").Value & .Range("c3").Value)
    End If
   
    'Beta Filter
    If Range("c4") = "" Then
    .Range("A7").AutoFilter Field:=4
    Else
    .Range("A7").AutoFilter Field:=4, Operator:=xlFilterValues, _
    Criteria1:=(Range("b4").Value & .Range("c4").Value)
    End If
   
    'Tenure Filter
    If Range("c5") = "" Then
    .Range("A7").AutoFilter Field:=5
    Else
    .Range("A7").AutoFilter Field:=5, Operator:=xlFilterValues, _
    Criteria1:=Array(Range("b5").Value & .Range("c5").Value, "*#*")
    End If
   
End With

End Sub

 

[Marmot3292]

Can anyone help?

 

[Marmot3292]

Alternatively I can use a formula in my data set to blank out the errors with "", however I would need to adjust the code to show the values selected plus blanks...