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

 

[Peter_SSs]

it will always be words

In that case, as Alex has pointed out, 'Find' is not going to work for you.

I tried finding the new code tags on your "signature block below" - what signature block are you referring to -

"signature block below" is the section of my posts (or MARK858's posts) below the pale line that stretches right across the posts. In particular, it is this part of my signature block that I was referring to

So that we know what we are dealing with & can easily copy it, can you post 8-10 rows of dummy (but realistic) sample data (including any variety of punctuation) and the expected results with XL2BB?
If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[Trebor76]

Hi Charles,

Try this where you would enter each word separated by a comma like "green,tree" (without the quotes) in TextBox1 as each word is used as filter:

Dim wsSrc As Worksheet
    Dim i As Long, j As Long
    Dim varWord As Variant
    
     If Len(Me.TextBox1.Value) = 0 Then
        Me.TextBox1.SetFocus
        MsgBox "No search word(s) have been entered.", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    ThisWorkbook.Sheets("VALSFOUND").UsedRange.ClearContents
    
    For Each varWord In Split(Me.TextBox1.Value, ",")
        With ThisWorkbook.Sheets("SOURCE")
            On Error Resume Next
                .ShowAllData
            On Error GoTo 0
            .Columns.EntireColumn.Hidden = False
            .Rows.EntireRow.Hidden = False
            i = IIf(i = 0, .Cells(Rows.Count, "C").End(xlUp).Row, i)
            .Range("C1:C" & i).AutoFilter Field:=3, Criteria1:="*" & CStr(varWord) & "*", Operator:=xlFilterValues
            j = IIf(j = 0, 1, ThisWorkbook.Sheets("VALSFOUND").Cells(Rows.Count, "A").End(xlUp).Row + 1)
            .Range("C1:C" & i).SpecialCells(xlCellTypeVisible).Copy Destination:=ThisWorkbook.Sheets("VALSFOUND").Range("A" & j)
        End With
    Next varWord
    
    i = Evaluate("IFERROR(COUNTA(VALSFOUND!A:A),0)")
    
    If i > 0 Then
        MsgBox "There were " & Format(i, "#,##0") & " verses found.", vbInformation
    Else
        MsgBox "There were no matching verses found.", vbExclamation
    End If
    
    Application.ScreenUpdating = True

It looks like you've gone back to Excel - what happened to the Access Db you were working on?

Regards,

Robert