Solved

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

Posted on 2013-11-07

Microsoft Excel

3 solutions

231 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 51

Accepted Solution

by:Rgonzo1971

Rgonzo1971 earned 300 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 30

Assisted Solution

by:gowflow

gowflow earned 100 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 100 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

Suggested Solutions

Title	# Comments	Views	Activity
Macro Modification to refresh non-contiguous Cells in a Worksheet	12	40	2017-05-12
Excel Auto-Complete lines	4	32	2017-05-15
Transfer Worksheet Data to Userform & Calculate based on Sum Range	28	46	2017-05-20
Chart to show data in four quadrants	6	24	1d

Spreadsheet Compare 2016 does not work with 64 Bit Office 2016

Article by: Stacy

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016

Automating Microsoft Excel from Access (Part I)

Article by: Dale Fye

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…

Building Probability Models in Excel Part 7: Modeling a Correlated Two-Fund Investment

Video by: Toby

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

Enumerating Prime Numbers in Excel / VBA

Video by: Ed

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11). The larger templa…