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

Marmot3292

New Member
Joined
Apr 5, 2019
Messages
20
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...

ScreenerErrorCapture.JPG



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?


Rich (BB code):
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Marmot3292

New Member
Joined
Apr 5, 2019
Messages
20
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,714
Messages
5,626,455
Members
416,186
Latest member
shamm28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top