Prev: Compiled exe hangs when closed
Next: Transparent Desktop Icon Text?
From: ErwinAlonzo on 17 Apr 2008 13:16
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.
From: Auric__ on 17 Apr 2008 14:02
On Thu, 17 Apr 2008 17:16:45 GMT, wrote:

> 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

--
You may think I am evil. I am not. I am efficient.
From: Auric__ on 17 Apr 2008 14:16
On Thu, 17 Apr 2008 18:02:30 GMT, I wrote:

> On Thu, 17 Apr 2008 17:16:45 GMT, wrote:
>
>> 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:

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.

--
She had accepted it once but now rejected it and could not give all of
her reasons for this change.
 | 
Pages: 1
Prev: Compiled exe hangs when closed
Next: Transparent Desktop Icon Text?