Excel VBA: store a word in a string in a variable if that word exists in an array of strings

Question

Suppose my line

my_string="MY PET IS CAT "

I have an array as well

my_animals=Array ("CAT","DOG","LION")

Since one of the lines in the array (CAT) is present in my_string, I want to have CAT in the variable.

How can we do this?

Answer 1

Dim my_string As String, my_animals As Variant, element As Variant, variable as String
my_string = "MY PET IS CAT "

my_animals = Array("CAT", "DOG", "LION")

For Each element In my_animals
  If InStr(my_string, element) Then
  variable = element
  End If
Next element

Answer 2

For posterity only, here (not particularly brilliant, but functional) is the way to add any elements my_animalsthat were found in my_string:

Public Sub FindAnimals

    dim my_string as String
    dim my_animals as Variant
    dim found(0) as String
    dim animal as String

    my_string="MY PETS ARE CAT AND LION"
    my_animals=Array ("CAT","DOG","LION")

    For Each animal in my_animals
       If InStr(my_string, animal) Then
           found(UBound(found)) = animal
           ReDim Preserve found(UBound(found) + 1)
       End If
    Next animal

End Sub

Once launched, it foundwill be an array Stringwith three elements:

• "CAT"
• "LION"
• ""

As I said, this is not the best method the world has ever seen, but it may be the starting point for something better.