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.
31764

[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