OzGrid

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

< Back to Search results

 Category: [Excel]  Demo Available 

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.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to use VBA code to convert date format
How to use a code to display the current date based on certain criteria
How to set dynamic dates for pivot table grouping
How to use VBA code to obtain date from cell, then calculate 3 months later

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 


Gallery



stars (0 Reviews)