Using pattern searches with a variable to find the correct values

[chazrab]

As stated. I tried to make this as short as possible, but the code block I included makes it a little long. At the
end of the day, the code should be able to find all values of a phrase similar, but this code doesn't:

4 translations of Gen. 1:29:

Behold, I have given you every herb bearing seed (KJV)
Then God said, "I give you every seed-bearing plant (NIV)
I have given you every plant yielding seed (NASB)
Behold, I have given you every plant yielding seed (RSV)

typed in a textbox: "seed bearing plant"

I want the code to be able to pull up all occurrences verses if I type in what I remember the verse says without being forced to type in exactly the phrase with spaces, commas, dashes, etc., and still have the code pick them up.

When I type in anything like "seed bearing plant" the code yields no results. How can that be? I don't know how to construct a pattern search with variable operators, i.e., using "like" or similar to ensure any similar phrase will be found every time:

This code does not work every time:

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 'this is a combobox value
Dim rngSrc As Range
With Worksheets("SOURCE") 'the sheet the code searches
Set rngSrc = .Range("C1", .Cells(Rows.count, "C").End(xlUp)) 'col C is the NASB column.  Sht is divided into 4 cols: KJV = A, NIV = B, NASB = C, RSV = D
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
lastrow = Sheets("VALSFOUND").Range("A" & Rows.count).End(xlUp).Row
Else
MsgBox "value not found"
End If
Sheets("VALSFOUND").Range("H1").Value = Me.cbav1.Value
Sheets("VALSFOUND").Range("I1").Value = Me.TextBox4.Value
End With
rowno = Sheets("VALSFOUND").Range("A1").End(xlDown).Row
Sheets("VALSFOUND").Range("H1").Value = rowno 'total rows found in search
Sheets("VALSFOUND").Range("I1").Value = X 'value to find, i.e.,, "last days"
ListBox1.ListIndex = 0
totrows = lastrow
Dim firstrow As Integer, myrange As Range
Set myrange = Sheets("VALSFOUND").Range("A1")
If myrange <> " " Then
firstrow = myrange.Row
Else
MsgBox "First row is blank"
End If
Sheets("VALSFOUND").Range("H1").Value = lastrow
Me.totrows.Value = lastrow
Me.rowno.Value = ListBox1.ListIndex + 1
Me.TextBox1.SetFocus
Me.TextBox1.CurLine = 0
Me.TextBox1.SelStart = 0
Sheets("VALSFOUND").Range("i1").Value = Me.cbav1.Value
Sheets("VALSFOUND").Range("J1").Value = Me.TextBox4.Value
End Sub

Thanks to anyone for help.

cr

 

[chazrab]

You spent a lot of time on this - thank you!. I really like this userform design. About the best I've seen for reporting and viewing results. It gives a complete view for any translation selected. I need to study this a while to understand all you've done, and test it with different
words and phrases so I can have this thing nailed down. A complete understanding of your and Trebor76's code is invaluable to me for this application. I knew this already, but translation differences will give different counts because of the way the original translators wrote it. A tremendous effort. Will get back to you as soon as I can.
Thanks for all your help.
cr, Kingwood, Tx

P.S.: I tested and retested and it works great. As mentioned above, different translations will give different counts of a word or phrase because
of the interpretation of the early translators. Not even the best code out there be it from Blue Letter Bible or Logos I don't think can capture exactly the correct result every time.
The form gives a complete view of the count and verses. Each time I change the version, the verses and count automatically update which is
real cool. Thanks again, Dante, for the work you put into this application. cr Kingwood, Tx