USING FIND METHOD WITH A VARIABLE TO FIND A STRING

[chazrab]

There are many FIND examples out there and they all work great - for finding ONE VALUE. There are more
times I want to find a phrase or string value, not just one word. The code I have below, familiar to all works well
for finding just a single word.

Private Sub cmdFINDVERSE_Click()
Sheets("RESULT").UsedRange.ClearContents
Dim x As String
Dim c As Range
Dim rw As Long
Dim firstaddress As Variant
Dim rowno As Integer
x = Me.TextBox6.Value  --->Textbox6 can be any single word or string value, either between quotes or not
With Worksheets("Sheet2").Range("H1:H31103")
Set c = .Find(x, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
rw = 1
firstaddress = c.Address
Do
Worksheets("Sheet2").Select
Range(Cells(c.Row, 5), Cells(c.Row, 9)).Copy Destination:=Sheets("RESULT").Range("A" & rw)
rw = rw + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
rowno = Sheets("RESULT").Range("A1").End(xlDown).Row
Sheets("RESULT").Range("G1").Value = rowno
Sheets("RESULT").Range("H1").Value = x
Me.TextBox7.Value = Sheets("RESULT").Range("G1").Value
ListBox2.ListIndex = 0
SEARCHRESULTS.Show
End Sub

Instead of typing the single word "appointed"(without quotes) in Textbox6 - which finds all occurrences
of the word appointed, how would I tell Excel to find ANY phrase or string, like "appointed time of the end",
or "latter days", or "sickness unto death", etc. The phrase or string can be in quotes but does not have to be.
Whatever makes the FIND more accurate.

This seems simple enough. I'm having trouble changing the code above to make sure it works with a phrase
or string, as mentioned. The two small screenshots below explain clearly what I'd like.

Thanks very much for anyone's help.
cr

 

[Alex Blakenburg]

What you have should work if you don't add the quotes to your search string.
If you wanted to play it safe you could add to the find string ,MatchCase:=False, SearchFormat:=False
but if they were your issue you would have the same issue with the single word search.

Maybe test your search string manually and then copy paste it into your text box to make sure it the exact same phrase.
Additional spaces or other small variations in the phrase would stop it being found.

 

[chazrab]

Hi Alex -
...everything works well just by typing in a phrase or string without any quotes.
I added the additional parameters as you suggested above. I'm confident
this code will FIND and display just about anything thing I type in.

Thanks for all your help.
cr
Kingwood, Tx

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[xdriver]

Any chance you can post the full working code without any extra lines or constraints. This looks like what I need

 

[Peter_SSs]

Any chance you can post the full working code without any extra lines or constraints. This looks like what I need

Duplicate to: VBA: Find a number of phrases in a sheet and highlight the row as long as one phrase is in it

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this old thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.