Solved

use split function to seperate values in textbox specific delimiter

Posted on 2013-05-10
14
300 Views
Last Modified: 2013-05-11
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
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
  • 7
14 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39158110
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39158119
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39158593
Martinless , If i put 1 value or 2 or more values in textbox33...it wipes out the whole data in listbox ?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 3

Author Comment

by:fordraiders
ID: 39158598
and i may only have one value sometimes.  sorry for the problems
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39158600
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39158743
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39158759
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39158793
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
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39158804
Here is my version. Does it do what you want?
listbox.xlsm
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39158816
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
 
LVL 48

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39158888
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39158897
Thanks very much working fine.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39158900
Thanks
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 39158921
You're welcome and again I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

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!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

628 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