Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Filter Items in listbox using Like or sometype of wilcard search in columns

Posted on 2013-11-07

Microsoft Excel

3 solutions

Medium Priority

239 Views

Last Modified: 2013-11-12

vba 2010 excel
variable to pass
str = "LOCTITE"

What I'm doing currently :
Removing rows in a listbox that match my criteria on Exact Strings
in this case
str = "LOCTITE"

Specifically in Columns 8 or 9

What I need to happen now:
My problem is sometimes in Column 8 and/or 9
The column may contain "LOCTITE CO." OR TRUE VALUE LOCTITE"

So i need the code to look in columns 8 and/or 9
If "LOCTITE" exists somewhere in the string .

Removing rows in a listbox that DO NOT CONTAIN my criteria.
in this case
str = "LOCTITE"

so all i will be left with is rows that DO CONTAIN in Columns 8 and/or 9 the word "LOCTITE"

' get rid of unwanted results that do not contain str
 Dim ri As Long
With UserForm2.ListBox1
    For ri = .ListCount - 1 To 0 Step -1
If .List(ri, 8) <> str Or .List(ri, 9) <> str Then
                    .RemoveItem ri
        End If
    Next
End With

Open in new window

Thanks
fordraiders

Comment

Question by:fordraiders

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

7 Comments

LVL 52

Accepted Solution

by:Rgonzo1971

Rgonzo1971 earned 1200 total points

ID: 396309062013-11-07

Hi,

pls try

' get rid of unwanted results that do not contain str
 Dim ri As Long
With UserForm2.ListBox1
    For ri = .ListCount - 1 To 0 Step -1
        If Not(.List(ri, 8) Like "*" & str & "*") _
            Or Not(.List(ri, 9) Like "*" & str & "*") Then
                    .RemoveItem ri
        End If
    Next
End With

Open in new window

Regards

LVL 31

Assisted Solution

by:gowflow

gowflow earned 400 total points

ID: 396309312013-11-07

For further refinement and in case you go thru some lowercase/uppercase replace this line

If Not(.List(ri, 8) like "*" & str & "*") Or Not(.List(ri, 9) Like "*" & str & "*") Then

Open in new window

by this line

If Not(lcase(.List(ri, 8)) like "*" & lcase(str) & "*") Or Not(lcase(.List(ri, 9)) Like "*" & lcase(str) & "*") Then

Open in new window

gowflow

LVL 1

Expert Comment

by:sisiliano

ID: 396310992013-11-07

If InStr(.List(ri, 8), str) = 0 or InStr(.List(ri, 9), str) =0 then

LVL 81

Assisted Solution

by:byundt

byundt earned 400 total points

ID: 396324372013-11-07

When testing sisiliano's suggestion of InStr, the comparison is case sensitive as posted--so "Loctite" won't match "TRUE VALUE LOCTITE". But you can make a case insensitive comparison with:

If InStr(1, .List(ri, 8), str, vbTextCompare) = 0 Then

Open in new window

LVL 1

Expert Comment

by:sisiliano

ID: 396325222013-11-07

Oops didnt think about that.

Hope byundt suggestion works for you though.

LVL 3

Author Comment

by:fordraiders

ID: 396431372013-11-12

goflow,

When testing your solution further.
If Not(lcase(.List(ri, 8)) like "*" & lcase(str) & "*") Or Not(lcase(.List(ri, 9)) Like "*" & lcase(str) & "*") Then
If Loctite is in 8 but not in 9...

It will still remove the line..

If its it one or the other then i want to keep the line.. ?

Thanks
fordraiders

LVL 81

Expert Comment

by:byundt

ID: 396431802013-11-12

It might be clearer to use an Else clause:

If (lcase(.List(ri, 8)) like "*" & lcase(str) & "*") Or (lcase(.List(ri, 9)) Like "*" & lcase(str) & "*")  Then
Else
     'Remove the line
End If

Open in new window

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

Dynamic Data Vaildation

Article by: Rob

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.

Building Probability Models in Excel Part 5: Modeling an Investment Using Discrete Random Variables

Video by: Toby

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…