use split function to seperate values in textbox specific delimiter

vba 2010

previous question for reference:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28120329.html



Userform:
 textbox
listbox


currently this code is taking the value in a textbox and if it does not exist in the loistbox in column 4 it deletes it from the listbox.




' the code is working fine...

What I need: I now want to use the Split function and using a ;(semi-colon) as a delimiter.
In the textbox...search for each value from the textbox getting rid of all the items in the listbox that do not contain the values from the textbox..

i.e. Textbox33 may have:

4-40;Hex;1/2;Steel

So all 4 criteria must exist somewhere in the column specified





 
Dim strFilter As String
Dim lngIndex As Long

strFilter = frmResultAll.TextBox33.Text

For lngIndex = ListBox32.ListCount - 1 To 0 Step -1
    If InStr(1, ListBox32.List(lngIndex, 3), strFilter) = 0 Then
        ListBox32.RemoveItem (lngIndex)
    End If
Next


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
OK try this.


Private Sub CommandButton1_Click()
Dim strFilter() As String
Dim lngIndex As Long
Dim lngPart As Long
Dim bFound As Boolean

strFilter = Split(TextBox33.Text, ";")

For lngIndex = ListBox1.ListCount - 1 To 0 Step -1
    bFound = True
    For lngPart = 0 To UBound(strFilter)
        If InStr(1, UCase(ListBox1.List(lngIndex, 3)), UCase(strFilter(lngPart))) = 0 Then
            bFound = False
            Exit For
        End If
    Next
    If Not bFound Then
        ListBox1.RemoveItem (lngIndex)
    End If
Next

End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Private Sub CommandButton1_Click()
Dim strFilter() As String
Dim lngIndex As Long
Dim lngPart As Long
Dim bFound As Boolean

strFilter = Split(TextBox33.Text, ";")

For lngIndex = ListBox1.ListCount - 1 To 0 Step -1
    bFound = False
    For lngPart = 0 To UBound(strFilter)
        If ListBox1.List(lngIndex, 3) = strFilter(lngPart) Then
            bFound = True
            Exit For
        End If
    Next
    If Not bFound Then
        ListBox1.RemoveItem (lngIndex)
    End If
Next

End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
That search is case sensitive. If you don't want it to be then change line 12 to

        If UCase(ListBox1.List(lngIndex, 3)) = UCase(strFilter(lngPart)) Then
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
FordraidersAuthor Commented:
Martinless , If i put 1 value or 2 or more values in textbox33...it wipes out the whole data in listbox ?
0
 
FordraidersAuthor Commented:
and i may only have one value sometimes.  sorry for the problems
0
 
Martin LissOlder than dirtCommented:
I assumed that if the listbox item does not contain 4-40 or Hex or 1/2 or Steel (or any other values that might be in the textbox) then that listitem should be deleted. Since that's apparently not what you want, please describe what should happen.
0
 
FordraidersAuthor Commented:
the other code was working fine..
"I assumed that if the listbox item does not contain 4-40 or Hex or 1/2 or Steel (or any other values that might be in the textbox) then that listitem should be deleted"
Yes this is what i want..

The listbox rows left would be rows that contain
4-40 or Hex or 1/2 or Steel

Thanks
0
 
Martin LissOlder than dirtCommented:
Martinless , If i put 1 value or 2 or more values in textbox33...it wipes out the whole data in listbox ?
I assume that you are describing a problem. If so please give me more detail.
0
 
FordraidersAuthor Commented:
no matter what i type in  textbox33   it will not filter correctly...
I see 4-40 in several rows in my listbox so it should retain those rows but deletes everyting.
0
 
Martin LissOlder than dirtCommented:
Here is my version. Does it do what you want?
listbox.xlsm
0
 
FordraidersAuthor Commented:
o, I see the communication problem.

I have descriptions.

Hex, 4-40 1/2-30 x Box Hex head
Hex, 5.20 1/2-20 x Square Head
Hex, 4-40 1/2-30 x Square head

so it want to filter
4-40;Square

I should only see
Hex, 4-40 1/2-30 x Square head

so it want to filter
4-40;Hex;Head

Hex, 4-40 1/2-30 x Box Hex head
Hex, 4-40 1/2-30 x Square head
0
 
FordraidersAuthor Commented:
Thanks very much working fine.
0
 
FordraidersAuthor Commented:
Thanks
0
 
Martin LissOlder than dirtCommented:
You're welcome and again I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
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.

All Courses

From novice to tech pro — start learning today.