How would i use the FIND method with the AND operator to find any two words in a sheet cell, i.e., "understanding" AND "heart"

[chazrab]

Title explains
This code works well enough but only finds one variable value of all the cells with the word"green"
I want the code to find all the cells with the twords "green" AND "tree". They can be separated and not be a phrase.
The code would find this cell that contains "there is a green apple on every tree" as well as "there are many green trees on this street".

Dim x as string
x = "green"
Sheets("VALSFOUND").UsedRange.ClearContents
  Dim lastrow, LastRow2 As Integer, X As String, C As Range, rw As Long, firstAddress As Variant, rowno As Variant, RownoA As Variant
          X = Textbox1.value
          With Worksheets("SOURCE").Range("C1:C31103") 'default NASB
                          Set C = .FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
                              If Not C Is Nothing Then
                                        rw = 1
                                        firstAddress = C.Address
                                        Do
                                            Worksheets("SOURCE").Select 'copy all the found values to sheet VALSFOUND
                                            C.Select
                                            Range(Cells(C.Row, 2), Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw) '
                                           rw = rw + 1
                                           Set C = .FindNext(C)
                                           Loop While Not C Is Nothing And C.Address <> firstAddress
                                            lastrow = Sheets("VALSFOUND").Range("A" & rows.count).End(xlUp).Row
                                            If lastrow = 1 Then
                                                    Range(Cells(C.Row, 2), Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw)
                                            Else
                                                     Range(Cells(C.Row, 2), Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & lastrow)
                                            End If
                                        Else
                       MsgBox "value not found"
                              End If
                  End With

Any help would greatly be appreciated,

Thanks, cr

 

[Jeffrey Mahoney]

You would need to create another set of variables like X and C. Add the 2 lines like the ones below and change the 3rd like below
Y = Textbox2.value
Set D = .FIND(Y, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not C Is Nothing and not D is Nothing Then

 

[chazrab]

You would need to create another set of variables like X and C. Add the 2 lines like the ones below and change the 3rd like below
Y = Textbox2.value
Set D = .FIND(Y, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not C Is Nothing and not D is Nothing Then

Hi Jeffrey - I did what you said as shown below. I had to use Ctrl + Fn+ B (Lenovo) to stop execution. I might mention that this code
is searching own 31,103 rows. If I use two words together, like "understand heart" or the prev. example = "green tree", the FIND method
works like a charm. It's when the two words are separated in the cell that makes the code a bit more complicated. If I could comment on this,
I thought I saw somehwere once that you can use FIND with two words using the AND operator, something like *"green"* AND ""tree"* but I could not find how to do this correctly anywhere on chatGPT, StackOverflow or any YT vids. Now, maybe I didn't finish the code correctly after I made your changes. Bolded comments below

Private Sub cmdFINDTWOWORDS_Click()
Sheets("VALSFOUND").UsedRange.ClearContents
  Dim lastrow, LastRow2 As Integer, X, y As String, C, D As Range, rw As Long, firstAddress As Variant, rowno As Variant, RownoA As Variant
          X = cbav1.Value
          y = TextBox4.Value
          With Worksheets("SOURCE").Range("C1:C31103") 'default NASB
                          Set C = .FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
                          Set D = .FIND(y, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
                              If Not C Is Nothing And Not D Is Nothing Then
                                        rw = 1
                                        firstAddress = C.Address
                                        Do
                                            Worksheets("SOURCE").Select
                                            C.Select
                                            Range(Cells(C.Row, 2), Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw) '
                                           rw = rw + 1 'bug here
                                           Set C = .FindNext(C) 
                                            Set D =FindNext(D) ' I did not add this line.  Maybe the cause for the endless loop
                                           Loop While Not C Is Nothing And C.Address <> firstAddress
                                            lastrow = Sheets("VALSFOUND").Range("A" & rows.count).End(xlUp).Row
                                            If lastrow = 1 Then
                                                    Range(Cells(C.Row, 2), Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw)
                                            Else
                                                     Range(Cells(C.Row, 2), Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & lastrow)
                                            End If
                                        Else
                       MsgBox "value not found"
                              End If
                  End With

Anyway, thanks for thring to help. I feel certain this can be done with any two words separated between other text in any cell.
cr

 

[Phuoc]

Try change:

 X = cbav1.Value
y = TextBox4.Value

to:

 X = cbav1.Value & "*" &  TextBox4.Value
y = TextBox4.Value  & "*" & cbav1.Value

 

[Alex Blakenburg]

Some of your code is a bit confusing and you have changed Textbox1 to cbav1 in your last version.
If you are sure that the text order will always be cbav1 followed by TextBox4 then you simple need to change X as suggested by @Phuoc to

 X = cbav1.Value & "*" &  TextBox4.Value

and you can ignore the below.

If you need to check the reverse order as well (which Phuoc has as Y) then I would not do a 2nd find.
Doing two finds in the one loop will get messy.
Simply find one of the values and then use Instr to check if it contains the 2nd value before actioning the copy.
Your code is modified below to do that, I made some other changes as well.
(I don't see what value the last If Then Else is doing and I have commented it out - remove if not required)

PS: If you are expecting a lot of results I would consider switching from using Find to using and Array.

Private Sub cmdFINDTWOWORDS_Click()

Sheets("VALSFOUND").UsedRange.ClearContents
Dim lastrow As Long
Dim X As String, C As Range, rw As Long, firstAddress As String
Dim Y As String
X = cbav1.Value
Y = Textbox4.Value

Dim rngSrc As Range
With Worksheets("SOURCE")
    Set rngSrc = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
End With

With Worksheets("SOURCE") 'default NASB
    Set C = rngSrc.Find(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
    If Not C Is Nothing Then
        rw = 1
        firstAddress = C.Address
        Do
            If InStr(1, C.Value, Y, vbTextCompare) > 0 Then
                .Range(.Cells(C.Row, 2), .Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw) '
                rw = rw + 1
            End If
            Set C = rngSrc.FindNext(C)
        Loop While C.Address <> firstAddress
        
'        --- Does not seem to do anything useful ---
'        lastrow = Sheets("VALSFOUND").Range("A" & Rows.Count).End(xlUp).Row
'        If lastrow = 1 Then
'                .Range(.Cells(C.Row, 2), .Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw)
'        Else
'                 .Range(.Cells(C.Row, 2), .Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & lastrow)
'        End If

    Else
        MsgBox "value not found"
    End If
End With
End Sub

 

[Peter_SSs]

Your thread title indicates that you are looking for "words" but your code is actually looking for "strings" meaning that if looking for "tree" it will return a cell that contains something like "I live in High Street". Can you clarify whether you are looking for words or strings?

I also suggest that you look at the newer tags for posting code rather that the old [code] ... [/code] tags. My signature block below has more details.

 

[chazrab]

Your thread title indicates that you are looking for "words" but your code is actually looking for "strings" meaning that if looking for "tree" it will return a cell that contains something like "I live in High Street". Can you clarify whether you are looking for words or strings?

I also suggest that you look at the newer tags for posting code rather that the old [code] ... [/code] tags. My signature block below has more details.

Hi Peter - if I understand you correctly, it will always be words, as in a complete word phrase, i.e., "green tree" or "many green and tall trees on the street"
but not "The Maltese Falcon starred Sydney Greenstreet" or "123 Kingsgreen Road" or "thewordtreelane is in this word" code would exclude these - but would only capture standalone words within cell text not as part of another word, whether separated between other text or as a complete phrase - like "the last days are nearing" and "I was in Hong Kong last night and many days have since passed" - correctly captures this sentence. I tried finding the new code tags on your "signature block below" - what signature block are you referring to - your name link below your image icon ? or the text links ? - just a little bit confused. Thanks for helping with this.
cr
Kingwood, Tx

 

[chazrab]

Some of your code is a bit confusing and you have changed Textbox1 to cbav1 in your last version.
If you are sure that the text order will always be cbav1 followed by TextBox4 then you simple need to change X as suggested by @Phuoc to

 X = cbav1.Value & "*" &  TextBox4.Value

and you can ignore the below.

If you need to check the reverse order as well (which Phuoc has as Y) then I would not do a 2nd find.
Doing two finds in the one loop will get messy.
Simply find one of the values and then use Instr to check if it contains the 2nd value before actioning the copy.
Your code is modified below to do that, I made some other changes as well.
(I don't see what value the last If Then Else is doing and I have commented it out - remove if not required)

PS: If you are expecting a lot of results I would consider switching from using Find to using and Array.

Private Sub cmdFINDTWOWORDS_Click()

Sheets("VALSFOUND").UsedRange.ClearContents
Dim lastrow As Long
Dim X As String, C As Range, rw As Long, firstAddress As String
Dim Y As String
X = cbav1.Value
Y = Textbox4.Value

Dim rngSrc As Range
With Worksheets("SOURCE")
    Set rngSrc = .Range("C1", .Cells(Rows.Count, "C").End(xlUp))
End With

With Worksheets("SOURCE") 'default NASB
    Set C = rngSrc.Find(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
    If Not C Is Nothing Then
        rw = 1
        firstAddress = C.Address
        Do
            If InStr(1, C.Value, Y, vbTextCompare) > 0 Then
                .Range(.Cells(C.Row, 2), .Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw) '
                rw = rw + 1
            End If
            Set C = rngSrc.FindNext(C)
        Loop While C.Address <> firstAddress
       
'        --- Does not seem to do anything useful ---
'        lastrow = Sheets("VALSFOUND").Range("A" & Rows.Count).End(xlUp).Row
'        If lastrow = 1 Then
'                .Range(.Cells(C.Row, 2), .Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & rw)
'        Else
'                 .Range(.Cells(C.Row, 2), .Cells(C.Row, 7)).Copy Destination:=Sheets("VALSFOUND").Range("A" & lastrow)
'        End If

    Else
        MsgBox "value not found"
    End If
End With
End Sub

Hi Alex, your revised code runs great but for some reason does not immediately display the results in Textbox1 of the userform, whereas my original code does. Should be a simple fix - more importantly, I'm confused about this reply line:
"If you are expecting a lot of results I would consider switching from using Find to using and Array." - my experience has been that the FIND method
takes a lot of time - as mentioned in reply to Jeffrey's post, any code, whether FIND or some code that uses an array will have to search down
31,103 rows of cell text - and in some cases, takes a while - a long time for VBA code to process - I am not familiar with using an array in place of FIND - as I understand array - that's just a way to store and process a solution and keep it in memory - and will display results much quicker - but I'm unsure if that's correct - in that light, how can an array replace the FIND method? On your other comment, the greatest amount of results in any search and display code I've ever written has been about 50, though that's not often.
Greatly appreciate your time and help -

cr

 

[MARK858]

I tried finding the new code tags

The new code tags are the icons at the top of the Posting/Reply window

just paste your code, select the code and click the icon, for normal VBA code use the

icon, for manually formatted code use

 

[Alex Blakenburg]

does not immediately display the results in Textbox1 of the userform, whereas my original code does.

In post #1 the only use of Textbox1 was X = Textbox1.value and that got replaced in Post #3 with X = cbav1.Value
Was there more to you code than you were showing us ? Can you show us the additional code ?
If your additional code was using the lastrow variable uncomment this line:
lastrow = Sheets("VALSFOUND").Range("A" & Rows.Count).End(xlUp).Row
Once we sort that out we can look at using an array and you might need @Peter_SSs to help with a Regex solution to avoid including part word matches in your results since both Find and Instr will include parts of a word. If your data doesn't have any punctuation we can get away without Regex but it sounds like you will mostly have some.