Converting Text Array Search from Cell to VBA Code

[goughar]

I've searched and can't find how to do what I'm looking for. I've posted a sample table below with the excel formula that gets what I need, and the VBA code I'm trying to use. Essentially, I need to use instr to search for Word1 OR Word2. Thanks for the help!

Excel Workbook
	B	C	D
2	Type	Description
3	356909	dogcatmouseelephant	yep
4	V49504	mouse
5	1009383	catdog
6	8403-2	elephantcat
7	3xjuh45	dogdeer	yep
8	203029	fox
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D3	=IF(AND(LEFT(B3,1)="3",COUNT(SEARCH({"cat","deer"},C3))),"yep","")
D4	=IF(AND(LEFT(B4,1)="3",COUNT(SEARCH({"cat","deer"},C4))),"yep","")
D5	=IF(AND(LEFT(B5,1)="3",COUNT(SEARCH({"cat","deer"},C5))),"yep","")
D6	=IF(AND(LEFT(B6,1)="3",COUNT(SEARCH({"cat","deer"},C6))),"yep","")
D7	=IF(AND(LEFT(B7,1)="3",COUNT(SEARCH({"cat","deer"},C7))),"yep","")
D8	=IF(AND(LEFT(B8,1)="3",COUNT(SEARCH({"cat","deer"},C8))),"yep","")

Option Compare Text

Sub Animal()

Do
    If Left(ActiveCell.Offset(0, -2), 1) = "3" And InStr(ActiveCell.Offset(0, -1), "cat") Then 'need to add an array with "cat" and "deer"...
    
        ActiveCell.Value = "Yep"
        ActiveCell.Offset(1, 0).Select
    
    Else
    
        ActiveCell.Offset(1, 0).Select
  
    End If
   
Loop Until IsEmpty(ActiveCell.Offset(0, -2))



End Sub

 

[VoG]

Try

If Left(ActiveCell.Offset(0, -2), 1) = "3" And (InStr(ActiveCell.Offset(0, -1), "cat") > 0 Or InStr(ActiveCell.Offset(0, -1), "deer") > 0) Then

 

[goughar]

Thanks for the help!

I tried your suggestion, but get the Object required error. I tried playing with it, but couldn't figure out why it won't work...

I also tried this and it did not work....

If Left(ActiveCell.Offset(0, -2), 1) = "3" And InStr(ActiveCell.Offset(0, -1), Array("cat", "deer"), 0) Then

 

[goughar]

Opps, I replied before seeing your 2nd suggestion. That one works exactly as I need.

Many thanks!

 

[VoG]

Sorry for the confusion. I deleted my original suggestion when I realised it wouldn't work.