How to If This (combo box value) Then That (Range Value) [Wildcards]

How to use IF/ This (combo box value) Then That (Range Value) [Wildcards]

Requirement:

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1203542-if-this-combo-box-value-then-that-range-value-wildcards

The user has a script as follows:

Code:

Private Sub cmdContact1_Click()

        Set DataSH = Sheet1
            DataSH.Range("O8") = Me.cboSelect.Value
            DataSH.Range("O9") = Me.txtSearch.Text
            'DataSH.Range("O9") = "*" & Me.txtSearch.Text & "*"
            DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
                "phonelist!Criteria"), CopyToRange:=Range("phonelist!Extract"), Unique:= _
                False
                ListBox1.RowSource = Sheet1.Range("outdata").Address(external:=True)

End Sub

Since the wildcards are only usable for text, the user is left with 3 cbo. Select values that will not search because they're numbers.

So if the combo box value:

Code:

DataSH.Range("O8")=Me.cboSelect.Value

Selected is any of these - NAME, DEPARTMENT, TITLE, UNIT, SHIFT, SUPERVISOR - the user needs to use the DataSH.Range that includes wildcards:

Code:

DataSH.Range("O9")="*"&Me.txtSearch.Text &"*"

But if the combo box value selected is any of these - EXTENSION, BUILDING, ROOM - the user need sto use the DataSH.Range without wildcards instead:

Code:

DataSH.Range("O9")=Me.txtSearch.Text

Solution:

Code:

Dim Ary As Variant
Ary = Array("NAME", "DEPARTMENT", "TITLE", "UNIT", "SHIFT", "SUPERVISOR")
If UBound(Filter(Ary, DataSH.Range("O8").Value, True, vbTextCompare)) >= 0 Then
    DataSH.Range("O9") = "*" & Me.txtSearch.Text & "*"
Else
    DataSH.Range("O9") = Me.txtSearch.Text
End If

Obtained from the OzGrid Help Forum.

Solution provided by la333.