Text search in VBA/Excel

[ErwinAlonzo]

Let me set up what I am attempting to do. My worksheet has one column
(A) which has a list of movie titles. I have a textbox for user input.
I’m trying to write a function to search the list for text written in
the textbox which then returns a true or false. I’ve found some code
on this page that does what I need almost perfectly (http://
vbadud.blogspot.com/2007/10/excel-vba-findall-method.html) except that
it works only for single words or exact phrases. If I have multiple
words in the textbox, I’d like to have all titles in the list with
*any* of the search words get flagged as true. For example, if the
search was “Nim’s Island”, I should get all cells with “Nim’s” or
“Island” flagged as true. Any help would be greatly appreciated.
Thanks.

 

[Auric__]

Let me set up what I am attempting to do. My worksheet has one
column (A) which has a list of movie titles. I have a textbox for
user input. I’m trying to write a function to search the list for
text written in the textbox which then returns a true or false.
I’ve found some code on this page that does what I need almost
perfectly (http://
vbadud.blogspot.com/2007/10/excel-vba-findall-method.html) except
that it works only for single words or exact phrases. If I have
multiple words in the textbox, I’d like to have all titles in the
list with *any* of the search words get flagged as true. For
example, if the search was “Nim’s Island”, I should get all cells
with “Nim’s” or “Island” flagged as true. Any help would be greatly
appreciated. Thanks.

This is just a 5-minute hack, similar to stuff I've done in the past:

Sub findMe(search As String)
ReDim finder(0) As String, found(0) As Long
Dim Loop0 As Long, Loop1 As Long

finder = Split(search) ' assumes words are space-separated
For Loop0 = 1 To ActiveCell.SpecialCells(xlCellTypeLastCell).Row
For Loop1 = 0 To UBound(finder)
If InStr(Cells(Loop0, 1).Formula, finder(Loop1)) Then
' this is where you do your "found" thing
' setting Bold is my usual "thing"
Cells(Loop0, 1).EntireRow.Font.Bold = True
GoTo shortCircuit
End If
Next
shortCircuit:
Next
End Sub

 

[Office_Novice]

To find Like values use * in Seach . If you want to find A title with the
words "The"
it would look like this Th* and the code will return all values with "Th"

Option Explicit
Option Compare Binary

Private Sub CommandButton1_Click()
Dim foundcell As Range
With Worksheets(1).Range("A1:A65536")



Set foundcell = .Find(What:=TextBox1.Value, lookAt:=xlWhole,
LookIn:=xlValues, SearchOrder:=xlRows)

On Error Resume Next
If Not foundcell Is Nothing Then
MsgBox foundcell 'Change all this to
do what you want
ElseIf foundcell Is Nothing Then
MsgBox "I did'nt find " & TextBox1.Value
End If
End With
End Sub

 

[Auric__]

This is just a 5-minute hack, similar to stuff I've done in the past:

Forgot to mention, this could take a bit. Might be helpful to put a
DoEvents right after the second "For". Maybe put some info in the status
bar so the user doesn't think it's hung.