Finding any two values in each cell text in a column of cells with both values as one Textbox1 variable

[chazrab]

As the title explains This code below works well enough if two variables x and y from two different textboxes are used:

    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim cell As Range
    Dim targetRow, lastrow As Long
    Dim x, y As String
   x = Textbox9.Value
   y = Textbox10.Value
    Set wsSource = ThisWorkbook.Sheets("Source") ' Change to your source sheet name
     Sheets("Result").UsedRange.ClearContents
    Set wsTarget = Sheets("Result") 'ThisWorkbook.Sheets.Add ' Creates a new sheet for the filtered data
    targetRow = 1 ' Initialize the target row
    For Each cell In wsSource.Range("E1:E31103")      'UsedRange
        If InStr(1, cell.Value, x, vbTextCompare) > 0 And InStr(1, cell.Value, y, vbTextCompare) > 0 Then
           cell.EntireRow.Copy wsTarget.Cells(targetRow, 1)
            targetRow = targetRow + 1
       End If
    Next cell
    MsgBox "Filtered data copied to Result sheet"
    lastrow = Sheets("Result").Range("B" & rows.count).End(xlUp).Row
    Me.TextBox3.Value = ListBox2.ListIndex + 1
    Me.TextBox4.Value = lastrow
    Sheets("Result").Range("H1").Value = lastrow
End Sub
[/ccode]

This line looks for the two variable values x and y from two different textboxes, Textbox9 and Textbox10.
[code]
If InStr(1, cell.Value, x, vbTextCompare) > 0 And InStr(1, cell.Value, y, vbTextCompare) > 0 Then...

I don't want two textboxes. I want to be able to type both values in one textbox
such as

x = Textbox1.value if Textbox1.value = "sun [and] moon" , OR "green [and] tree"
and give the same result as having each value  in two separate textboxes
I'm having a tough time figuring out how to change the InStr code line to do that 

Any help would be appreciated. 
cr

 

[dreid1011]

What are the possible combinations of input format? (IE: value1 and value2, value1 & value2, value1 value2, etc.) Are the values limited to single words?

 

[chazrab]

What are the possible combinations of input format? (IE: value1 and value2, value1 & value2, value1 value2, etc.) Are the values limited to single words?

only one = 'and' sun AND dark, or green AND tree. I want to be able to type any two words with the word 'and' between and the code, if
correct should find all cells in a column that CONTAIN both words.

 

[dreid1011]

Give this change a try:

Dim x, y As String
   x = Textbox9.Value
   y = Textbox10.Value

to:

Dim x As String, y As String, arr() As String
arr() = Split(Replace(Textbox9.Value, " and ", " "), " ")
x = arr(0)
y = arr(1)

 

[chazrab]

A little confused, so i made an image of the form. Description explains. I'm not sure where your corrected code fits into
the code block I originally sent. Textbox1 at the top is the way I'd like to make it work - with just a single textbox containing both terms or words.

cr

 

[dreid1011]

A little confused, so i made an image of the form. Description explains. I'm not sure where your corrected code fits into
the code block I originally sent. Textbox1 at the top is the way I'd like to make it work - with just a single textbox containing both terms or words.

cr

It replaces the lines of your code I showed in #4:

Dim x, y As String
   x = Textbox9.Value
   y = Textbox10.Value

Your lines of code ^

to:

Dim x As String, y As String, arr() As String
arr() = Split(Replace(Textbox1.Value, " and ", " "), " ")
x = arr(0)
y = arr(1)

Replace with my lines of code ^

You are typing into Textbox1 and not Textbox9?

 

[chazrab]

Correct. When the code works as it should, Textboxes 9 and 10 will be permanently deleted. Too much clutter on the form for me. I like
to keep things as simple as possible. Traditional applications allow any single word - "green":, phrase - "last days",
two words separated between text - "the quick "brown" fox jumped over the "fence". If I type in brown fence just like this in Textbox1, with no
quotes or commas, it should pick up every cell that contains these two words or any two words, whether next to each other
or separated by other text. If punctuation is needed to make it work, no problem.
That way, every word form can be found.

BTW - thanks for all your help on this.
cr

 

[dreid1011]

Did you replace the lines of code I indicated with what I suggested?

 

[chazrab]

..yes. After posted my reply, i followed your directions - and it works great!
Thanks again, for all your help.
cr

 

[dreid1011]

You're welcome and happy to help. It will also accept two words without "and" between.