Solved: make a dynamic if statment?

[Ago]

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

[mikerickson]

This is one way to test against multiple strings.

Dim passArrayToSheet As Variant

Sub test()
Dim excludedWords As Variant
Dim testString As String
testString = "Apple Pie Cinnamon"
excludedWords = Array("cat", "dog", "app")

Rem set the value of the UDF PassArray()
passArrayToSheet = excludedWords

If CBool(Evaluate("=SUMPRODUCT(--ISNUMBER(SEARCH(PassArray()," & Chr(34) & testString & Chr(34) & ")))")) Then
MsgBox "One of the excluded words is in the test string"
Else
MsgBox "None of the excluded words is a sub-string of the test string"
End If
End Sub

Private Function PassArray() As Variant
PassArray = passArrayToSheet
End Function

[JimmyTheHand]

And this is another way.

Function ValidString(S As String, Optional Exclusions) As Boolean
Dim i As Long
ValidString = True
If IsMissing(Exclusions) Then Exit Function
If IsArray(Exclusions) Then
For i = LBound(Exclusions) To UBound(Exclusions)
If InStr(S, Exclusions(i)) > 0 Then ValidString = False
Next
Else
If InStr(S, Exclusions) > 0 Then ValidString = False
End If
End Function

Parameter Exclusions is possibly array, that can be created from the textbox by using Split method. Assuming that, in the textbox, the words to be excluded are separated by commas:
Exclusions = Split(UserForm1.TextBox1.Value, ",") Also, I would suggest using Find method for searching for strings. It will be substantially faster, especially in case of large cell ranges.

So the full code would be something like this
Private Sub CommandButton1_Click()
Dim sht As Worksheet, SearchRange As Range, c As Range, FirstAddress As String
Dim Exclude, MC As Boolean, IPrng, testString, FirstItem As Boolean

Set sht = Sheets("Big IP collection")
Set SearchRange = sht.Range("G6", sht.Range("G" & Rows.Count).End(xlUp))

MC = UserForm1.CheckBox1.Value
UserForm1.TextBox2.Text = ""
Exclude = Split(UserForm1.TextBox1.Value, ",")
FirstItem = True
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 Not FirstItem Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) & _
Chr(10) & IPrng
Else
UserForm1.TextBox2.Text = IPrng
End If
With SearchRange
Set c = .Find(IPrng, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=MC)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If ValidString(c.Offset(, -2).Value, MC, Exclude) And _
c.Offset(, -2).Value <> prev Then
UserForm1.TextBox2.Text = UserForm1.TextBox2.Text & Chr(10) _
& c.Offset(, -2).Value
prev = c.Offset(, -2).Value
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
FirstItem = False
End If
Next i
End Sub


Function ValidString(ByVal S As String, ByVal MatchCase As Boolean, Optional ByVal Exclusions) As Boolean
Dim i As Long
ValidString = True
If Not MatchCase Then S = UCase(S)

If IsMissing(Exclusions) Then Exit Function
If IsArray(Exclusions) Then
For i = LBound(Exclusions) To UBound(Exclusions)
If Not MatchCase Then Exclusions(i) = UCase(Exclusions(i))
If InStr(S, Exclusions(i)) > 0 Then ValidString = False
Next
Else
If Not MatchCase Then Exclusions = UCase(Exclusions)
If InStr(S, Exclusions) > 0 Then ValidString = False
End If
End Function

Note #1: I didn't test the code.
Note #2: Your code looks for the values in column G but looks for the excluded values in column E. Is this OK?

Jimmy

[Ago]

Thank you so much Jimmy!
you are right it was much faster with find! and it work perfect!
just had some problems with firstadress.

firstaddress and c.address was by some strange reason incompatible types.
by making it a string it worked.
i also thought c.address was a range.

and one small typo, MatchMase:=MC.

i really appreciate the help!

[JimmyTheHand]

You are welcome, and also thank you for pointing out those typos. I've corrected them since.
Excel was right to dislike FirstAddress as range. A cell is a range, but the cell's address is something like "$A$1", and this is obviously a string. When you see
Range("$A$1") it means the cell with the address of "$A$1". Surely, the above expression returns a range, but the address is still a string, anyway.

Jimmy