Search/Match/Find ANY part of string to ANY part of Cell Value
 

I am trying to setup an Assessment style spreadsheet that can have multiple
configurations. Criteria is selected from the "Criteria" worksheet and then
compared against the elements pertaining to that criteria in another
worksheet. If the search criteria is NOT found, the entire line is hidden.

In my range, column 6 contains the Report Type, column 7, contains the
filter options, and column 8 contains advanced filter options.

User may only select one report type, but can select multiple filters and /
or advanced filters.

I have concatenated the filter(s) selection into one string:
strSearchFilter = Range("Filter1") & "*" & Range("Filter2") & _
"*" & Range("Filter3") & "*" & Range("Filter4") & _
"*" & Range("Filter5") & "*" & Range("Filter6")
I then concatenat the advanced filter options into another string:
strSearchAdvFilter = Range("AdvFilt1") & "*" & Range("AdvFilt2") & "*" _
& Range("AdvFilt3") & "*" & Range("AdvFilt4") &
"*" _
& Range("AdvFilt5") & "*" & Range("AdvFilt6") &
"*" _
& Range("AdvFilt7") & "*" & Range("AdvFilt8") &
"*" _
& Range("AdvFilt9") & "*" & Range("AdvFilt10")

The actual cell referenced contains all of the possibilities for each
filter, for the criteria being assessed, seperated with an asterik:
ALL*CE*COS*D&D*OTC*QMS

Or maybe less: ALL*INS*CEM*INP

Or, even only one option: ALL

So, if any part of the concatenated string matches any part of the cell
reference, it doesn't get hidden.

Here's the code I'm using. It sort of works. The Report Type is found, but
none of the filter options are found, and of course, they do actually
exist....

Here's the code:

QsrSetReportType:
'Check for Valid Report Type selected
'Displays error if Report Type is not selected
'Places user at the Report Type to make selection
If Range("ReportType") = "- Select Type -" Then
MsgBox "You Must Select A Report Type", vbOKOnly, "Report Type Error"
Range("ReportType").Worksheet.Activate
Range("ReportType").Select
Exit Sub
End If
'Hide All Rows Not Associated with Selected Report Type(s) - Sheet QSR
strSearchType = Range("ReportType")
strSearchFilter = Range("Filter1") & "*" & Range("Filter2") & _
"*" & Range("Filter3") & "*" & Range("Filter4") & _
"*" & Range("Filter5") & "*" & Range("Filter6")
strSearchAdvFilter = Range("AdvFilt1") & "*" & Range("AdvFilt2") & "*" _
& Range("AdvFilt3") & "*" & Range("AdvFilt4") &
"*" _
& Range("AdvFilt5") & "*" & Range("AdvFilt6") &
"*" _
& Range("AdvFilt7") & "*" & Range("AdvFilt8") &
"*" _
& Range("AdvFilt9") & "*" & Range("AdvFilt10")
lngTypeFound = 0
lngFilterFound = 0
lngAdvFilterFound = 0
For QsrLoop = 1 To Range("QsrCriteria").Rows.Count
strSearchRepType = Range("QsrCriteria").Rows(QsrLoop).Columns(6).Valu e
strSearchRepFilter =
Range("QsrCriteria").Rows(QsrLoop).Columns(7).Valu e
strSearchRepAdvFilter =
Range("QsrCriteria").Rows(QsrLoop).Columns(8).Valu e
Range("QsrCriteria").Rows(QsrLoop).Columns(6).Sele ct
lngTypeFound = InStr(1, strSearchRepType, strSearchType,
vbTextCompare) 'Returns starting position of search string
lngFilterFound = InStr(1, strSearchRepFilter, strSearchFilter,
vbTextCompare) 'Returns starting position of search string
lngAdvFilterFound = InStr(1, strSearchRepAdvFilter,
strSearchAdvFilter, vbTextCompare) 'Returns starting position of search string
MsgBox "Search Type = " & strSearchType & Chr$(13) & _
"Type Found @ " & lngTypeFound & Chr$(13) & _
"Search Filter = " & strSearchFilter & Chr$(13) & _
"Filter Found @ " & lngFilterFound & Chr$(13) & _
"Search Adv Filter = " & strSearchAdvFilter & _
"Advanced Filter Found @ " & lngAdvFilterFound
If lngTypeFound = 0 And strSearchRepType < "Heading" And
strSearchRepType < "Empty" Then
wrkshtProtect "Unprotect"
ActiveCell.EntireRow.Hidden = True
wrkshtProtect "Protect"
End If
Next QsrLoop
Return