Improve company productivity with a Business Account.Sign Up

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

244 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

7 Comments

LVL 55

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

A Game of Draw Poker

Article by: Martin

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.

Zip and unzip files and folders with VBA the Windows Explorer way

Article by: Gustav

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.

Creating a Double-Sided Bar Chart in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

Microsoft Excel

How to Add a "Save As" icon to the Quick Access Toolbar

Video by: crystal

How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…