Ago
06-22-2008, 02:25 AM
i have created a userform where the user can search.
the user also has a option to exclude some values.
so if they want to search for water but not waterballons, they could type ballones in the TextBox.
my plan is to let the user make more than one exclusions (if they want).
so if they type in the TextBox "ballons;hose" i want it to exclude those words from the search.
i got it working with excluding one word, but how do i make the if statement dynamic to handle more exclusions?
the statement looks like this so far.
if upper=1 is if the user has chosen it to be casesensitive.
IPrng is the actual value the user searches for.
prev keeps track of previous added value, so if i have waterslide twice in the list only one will show up in the searchresult.
"and not teststring like exclude" thats the part i want dynamic.
While a < n1
If upper = 1 Then
testString = UCase(sht.Range("E" & a).Value)
Else
testString = sht.Range("E" & a).Value
End If
If sht.Range("G" & a).Value Like IPrng And sht.Range("E" & a).Value <> prev _
And Not testString Like exclude Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) & sht.Range("E" & a).Value
prev = sht.Range("E" & a).Value
End If
a = a + 1
Wend
so how can i make a
And Not testString Like exclude2 Then
And Not testString Like exclude3 Then
and so on?
i think the most common is to have one or two exclusions, more than four exclusions is very very unlikely.
below is the whole code if that is needed.
Private Sub CommandButton1_Click()
Dim sht As Worksheet
Set sht = Sheets("Big IP collection")
n1 = sht.Cells(Rows.Count, 5).End(xlUp).Row + 1
a = 6
exclude = UserForm1.TextBox1.Value
If exclude <> "" Then exclude = "*" + exclude + "*"
If UserForm1.CheckBox1.Value = False Then
upper = 1
exclude = UCase(exclude)
End If
UserForm1.TextBox2.Text = ""
If ListBox1.ListIndex = -1 Then Exit Sub
For i = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) = True Then
IPrng = ListBox1.List(i)
If IPrng = "" Then Exit Sub
IPrng = IPrng + "*"
If NotFirst = 1 Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) & _
Chr(10) & IPrng
Else
UserForm1.TextBox2.Text = IPrng
End If
While a < n1
If upper = 1 Then
testString = UCase(sht.Range("E" & a).Value)
Else
testString = sht.Range("E" & a).Value
End If
If sht.Range("G" & a).Value Like IPrng And sht.Range("E" & _
a).Value <> prev And Not testString Like exclude Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) _
& sht.Range("E" & a).Value
prev = sht.Range("E" & a).Value
End If
a = a + 1
Wend
a = 6
NotFirst = 1
End If
Next i
End Sub
Reformatted code to stop screen from side-scrolling.
~Oorang
the user also has a option to exclude some values.
so if they want to search for water but not waterballons, they could type ballones in the TextBox.
my plan is to let the user make more than one exclusions (if they want).
so if they type in the TextBox "ballons;hose" i want it to exclude those words from the search.
i got it working with excluding one word, but how do i make the if statement dynamic to handle more exclusions?
the statement looks like this so far.
if upper=1 is if the user has chosen it to be casesensitive.
IPrng is the actual value the user searches for.
prev keeps track of previous added value, so if i have waterslide twice in the list only one will show up in the searchresult.
"and not teststring like exclude" thats the part i want dynamic.
While a < n1
If upper = 1 Then
testString = UCase(sht.Range("E" & a).Value)
Else
testString = sht.Range("E" & a).Value
End If
If sht.Range("G" & a).Value Like IPrng And sht.Range("E" & a).Value <> prev _
And Not testString Like exclude Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) & sht.Range("E" & a).Value
prev = sht.Range("E" & a).Value
End If
a = a + 1
Wend
so how can i make a
And Not testString Like exclude2 Then
And Not testString Like exclude3 Then
and so on?
i think the most common is to have one or two exclusions, more than four exclusions is very very unlikely.
below is the whole code if that is needed.
Private Sub CommandButton1_Click()
Dim sht As Worksheet
Set sht = Sheets("Big IP collection")
n1 = sht.Cells(Rows.Count, 5).End(xlUp).Row + 1
a = 6
exclude = UserForm1.TextBox1.Value
If exclude <> "" Then exclude = "*" + exclude + "*"
If UserForm1.CheckBox1.Value = False Then
upper = 1
exclude = UCase(exclude)
End If
UserForm1.TextBox2.Text = ""
If ListBox1.ListIndex = -1 Then Exit Sub
For i = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) = True Then
IPrng = ListBox1.List(i)
If IPrng = "" Then Exit Sub
IPrng = IPrng + "*"
If NotFirst = 1 Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) & _
Chr(10) & IPrng
Else
UserForm1.TextBox2.Text = IPrng
End If
While a < n1
If upper = 1 Then
testString = UCase(sht.Range("E" & a).Value)
Else
testString = sht.Range("E" & a).Value
End If
If sht.Range("G" & a).Value Like IPrng And sht.Range("E" & _
a).Value <> prev And Not testString Like exclude Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) _
& sht.Range("E" & a).Value
prev = sht.Range("E" & a).Value
End If
a = a + 1
Wend
a = 6
NotFirst = 1
End If
Next i
End Sub
Reformatted code to stop screen from side-scrolling.
~Oorang