I think its a string problem?

[Ryanthomsn]

Hello Everyone,

1. I am trying to sort out a couple different words from a column of strings. Most examples I have seen online only use one phrase that occupies the full cell (like the "dog" & "cat" in lines 3 and 5 below) that then is used to fill in a term or variable in column B. I want to do that but also identify the string if its got the same term but in a phrase or sentence, such as line 2 cause its got the term "dog" in it. Sometimes these sentences have the term at the beginning, end or somewhere in the middle of the phrase or sentence so its hard to say where it is but its there. It also has to go through a couple thousand of lines so I thought making an array would slow it down to much.

2. The other issue is I want t6o add more criteria (or different types of pets) but lik A LOT more! I would imagine the line[If a="dog" Or a="hamster" Then] would get super congested, long and difficult to read if I just kept adding more "Or" statements to it. I was wandering if there could be a better way of applying more criteria to that line?

Sub Testing()
Range("B2:B6").ClearContents


For i = 2 To 6
    a = Cells(i, 1).Text
    If a = "dog" Or a = "hamster" Then
        Cells(i, 2) = "My pet is hungry"
    ElseIf a = "cat" Then
        Cells(i, 2) = "My neighbors pet is hungry"
    End If
Next i


End Sub

The picture shows line 3 & 5 are working but I would like to get lines 2,4, & 6 working as well. Thank you in advance for the advice.
Screenshot 2024-08-22 174822.png

[Paul_Hossler]

Couple of ways to try

Option Explicit


Sub Testing()
    Dim i As Long
    
    With ActiveSheet
        .Range("B2:B6").ClearContents




        For i = 2 To 6
            If InStr(UCase(.Cells(i, 1).Value), "DOG") > 0 Then
                .Cells(i, 2) = "My pet is hungry"
            ElseIf InStr(UCase(.Cells(i, 1).Value), "HAMSTER") > 0 Then
                .Cells(i, 2) = "My pet is hungry"
            ElseIf InStr(UCase(.Cells(i, 1).Value), "CAT") > 0 Then
                .Cells(i, 2) = "My neighbors pet is hungry"
            End If
        Next i
    End With


End Sub


Sub Testing2()
    Dim i As Long, j As Long
    
    Dim A1 As Variant, A2 As Variant
    
    A1 = Array("DOG", "HAMSTER")
    A2 = Array("CAT", "BIRD")
    
    
    With ActiveSheet
        .Range("B2:B6").ClearContents


        For i = 2 To 6
            For j = LBound(A1) To UBound(A1)
                If InStr(UCase(.Cells(i, 1).Value), A1(j)) > 0 Then
                    .Cells(i, 2) = "My pet is hungry"
                    Exit For
                End If
            Next j
            
            For j = LBound(A2) To UBound(A2)
                If InStr(UCase(.Cells(i, 1).Value), A2(j)) > 0 Then
                    .Cells(i, 2) = "My neighbors pet is hungry"
                    Exit For
                End If
            Next j
        Next i
    End With


End Sub

[Ryanthomsn]

Thanks Paul_Hossler! It works great and you've given me some new functions I've got to read up on. Thanks