VBA: If cell contains word (from a list) then return a specific value

[Kikisinche]

The following scenario was already addressed on another post:
In the following table I would like the code to return “Fruits” if the cell contains a word that refers to a fruit (i.e. apples, oranges, pears)

I love apples	Fruits
I like oranges	Fruits
I bla bla lettuce	Veggies
I hate milk	Drinks
I blabla pears	Fruits
I blabla books	“NOT LISTED”

<tbody>
</tbody>

I would like to add some more categories, for example:
for example "Vegetables" that would include Lettuce, Tomato then print "Veggies”, another category could be caller “Drinks” and would include milk, coffee..
In addition, let’s say that in case the content of the cell tested is not matching with any of these categories, it would return “NOT LISTED”


I know that in the initial example, only the fruits were included and the code was the one listed below, I am just not too sure on how to integrate the 2nd, 3rd categories as well as the message in case nothing is matching.

Sub Fruit()
Dim LR As Long, i As Long, j As Long, Fruits
Fruits = Array("apple", "orange", "pear")
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("A" & i)
For j = LBound(Fruits) To UBound(Fruits)
If .Value Like "*" & Fruits(j) & "*" Then
.Offset(, 1).Value = Fruits
Exit For
End If
Next j
End With
Next i
End Sub




I would love to get clarification on this.
Thanks so much for your time/help,
Kev

 

[footoo]

for example "Vegetables" that would include Lettuce, Tomato

Tomato is a fruit, not a vegetable.

Perhaps you could have a two column list on a separate sheet (hidden if desired) that has all categories and items.
Then loop the list for each cell in the range to find the item and return the category.