deleting rows from listbox based on search term criteria

excel vba 2010
userform listbox32
userform textbox33

What I have:
I have a textbox33 I'm allowing people to type in a search term to see if it exists in 2 columns in the listbox.(terms SEPERATED BY A  ;  

So a textbox33 could contain
RED;10;WOOD;


code:
strFilter = Split(TextBox33.Text, ";")
For lngIndex = ListBox32.ListCount - 1 To 0 Step -1
    bFound = True

    For lngPart = 0 To UBound(strFilter) ' VALUES TO SEARCH FOR
       ' assuming bfound is still true
      If InStr(1, UCase(ListBox32.List(lngIndex, 3)), UCase(strFilter(lngPart))) = 0 Or InStr(1, UCase(ListBox32.List(lngIndex, 4)), UCase(strFilter(lngPart))) = 0 Then
                                bFound = False
                                Exit For
                             End If
                           
    If Not bFound Then
        ListBox32.RemoveItem (lngIndex)
    End If
Next

Open in new window



What I need:
if a person types in more than 1 search term. The code becomes more complicated.
Basically, I need this.
All 3 search terms  has to exist in both columns.
right now i'm using an OR

so  in this case "RED;10;WOOD;"  would have to exist in both columns in all rows.
if any of the terms do not exist in either column.
that row gets deleted from the listbox.


it may be as simple as changing the operator to an "AND" ??

just trying to make sure.


thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
it may be as simple as changing the operator to an "AND" ??

yes it is!
0
 
FordraidersAuthor Commented:
ok, but let me explain again..

Listbox32  columns 3  and   4

Column 3                                         Column 4
AIR TUBING 1/2 X 3/4 COPPER        AIR TUBING 1/2 X 3/4 COPPER 722 PRESSURE
TUBING 1/2 X 1/2 WROT                 AIR TUBING 1/2 X 1/2 WROT COPPER 322 PSI
TUBING 3/4 X 7/16                          TUBING 3/4 X 7/16 WROT 322 PSI

if my search terms were:
1/2;WROT

then row 1 would be the only row to get deleted:
because even though 1/2 exists in column 3 and 4
WROT does not exist in  column 3 and 4


Thanks
fordraiders
0
 
Rgonzo1971Commented:
Hi

In your example you say that line 3 should not be deleted  but hasn't 1/2

Could you explain?

Regards
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
FordraidersAuthor Commented:
because it has the word "wrot" in column 4
0
 
Rgonzo1971Commented:
You say that line 3 should not be deleted because it has WROT but not 1/2

so Why line 1 should be deleted if it has 1/2 and not WROT

Regards
0
 
FordraidersAuthor Commented:
sorry line 3 should be deleted also...my fault.
0
 
Rgonzo1971Commented:
So I do not see the problem in your code

it triggers a delete as soon as it doesn't find one of the search terms in any of the columns

Isn't it what you want?

Regards
0
 
FordraidersAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.