Text search function for VBA/Excel?

Question

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

The problem is 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.

Answer 1

1. get search text from textbox into arText=split(textbox1.value, ",") , use comma or space is up to you

2. modify the Function FindAll() change ByVal sText As String to ByVal arsText() As String

3. wrap the find lot in the function with a for next loop or for each loop using ubound(arsText) as limit, change What:=sText to What:=arsText(i)

when you use the function, arMatches(iArr) will have all address of the words, it might has duplicate addresses as in the case search string is "Nim’s Island”, 1 for each word search

4. do a bubble sort with the arsText() then loop thru arsText() to see if elements i and i+1 are the same address, if yes, eliminate duplicates

Hope this helps.

Answer 2

on the subject of your records lay-out: Assuming "Adams" is in A1 ---IF the contents of B3 are "24A, 11B, 12" Then =journey("*11b*", B:B, 0) ought to return 3 (word the case insensitivity) ---- If B3 is "24A", C3 "11B" and D3 "12" Then the CSE formula =journey("*11b*", B1:B10&C1:C10&D1:D10, 0) will return 3 word that this formula must be shown with Ctlr-Shift-enter (Cmd+return for Mac) additionally the ranges B1:B10, C1:C10, D1:D10 could be accelerated, yet a) can not be finished column refereneces (eg. no longer B:B) b) ought to all be an identical length. Which ever the strategies lay-out is, =INDEX(A1:A10, journey(yourversion), a million) will return the call you seek for. The A1:A10 variety ought to journey the style used on your journey function.

Answer 3

You will have to parse the input string, I assume based on space, and search for each item found.