Excel - VBA : make Match method less restrictive (by finding a word in a sentence)

Question

I am using the match function in my excel program but I have a problem with it as it only selects the identical matches. For example, if you compare a cell with "Banana" to another cell with "Banana", it's gonna work and return a positive value.
But if you compare "Banana" to a cell whose content is "Banana choco", then it's not gonna recognize that the word banana is in the cell.

In my case, I would like to return a TRUE value whenever a word is spotted in a sentence.

Here is my code :

Worksheets("sBldgMakati").Activate

For i = 2 To 605
  Range("B" & i).Activate
  myResult = IsNumeric(Application.Match(ActiveCell.Value, elementsListRange, 0))

   If myResult Then
   Range("K" & i).Value = Range("K" & i).Value + 10
   Else
   Range("K" & i).Value = Range("K" & i).Value + 0
End If

Next i  

I have to specify that in this code, elementsListRange correspond to a range of cells whose content is only one word ("Banana" for example) and the ActiveCell.value is usually a longer expression ("Banana choco" for example).

Thanks in advance for your help !

Answer 1

From the help documentation in Excel (if you press F1 and search for match):

If match_type is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*) — in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters.

Thus, =MATCH("*Banana*",C8,0) returns 1 if C8 contains "Banana choco".

In VBA, if the item to be matched is in a cell, you can include the wildcard asterisks with string concatenation, e.g.:

myResult = IsNumeric(Application.Match("*" & ActiveCell.Value & "*", elementsListRange, 0))

If there are many sentences in the range B2:B605 that have to be tested against all the words in elementListRange, as appear to be the case in this situation, I think you may need to do something like this (untested):

For Each cell in elementsListRange.Cells
    myResult = Application.Match("*" & cell.Value & "*", B2:B605, 0)
    if (IsNumeric(myResult)) then
        Range("K" & myResult).Value = Range("K" & myResult).Value + 10
    end if
next