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

x
?
Solved

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

Posted on 2013-11-07
7
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
0
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
7 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1200 total points
ID: 39630906
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
0
 
LVL 31

Assisted Solution

by:gowflow
gowflow earned 400 total points
ID: 39630931
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
0
 
LVL 1

Expert Comment

by:sisiliano
ID: 39631099
If InStr(.List(ri, 8), str) = 0 or  InStr(.List(ri, 9), str) =0 then
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Assisted Solution

by:byundt
byundt earned 400 total points
ID: 39632437
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

0
 
LVL 1

Expert Comment

by:sisiliano
ID: 39632522
Oops didnt think about that.

Hope byundt suggestion works for you though.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39643137
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
0
 
LVL 81

Expert Comment

by:byundt
ID: 39643180
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

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
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.
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question