Run-Time Error 1004 Accessing Worksheet Function in VBA with Error Handling-CodePudding

The code provided below is intended to highlight cells where a negative word is within 4 words of a key word. For the sake of my benefactors privacy I will not provide the keywords I search for however you can see the function of the code. You could put any word as a key word for testing including but not limited to "Micheal", "building", "damage", etc. The reason for using the worksheet application search in the keyword matching was to make sure that words that are plural don't go unflagged. The issue I'm having is that when I run the code, it stops if the individual words being held from the Temp string don't match the key words provided. What I need it to do is if the individual word from the Temp String doesn't match a keyword than the for loop should go to the next jth key word. Some help would be greatly appreciated. Error Handling is unfamiliar territory for me.

Sub IdentifyCameraPresence()
    Application.ScreenUpdating = False
    
    Dim Rng As Range
    Dim x As Double, y As Double, i As Double
    Dim Negatives As Variant, Keys As Variant, n As Double, k As Double 'keeps track of the location of negatives and key words in a cell
    Dim NWords As Variant, KWords As Variant, m As Double, j As Double 'keeps track of the words that are negative and key
    Dim Temp As Variant
    
    Set Negatives = CreateObject("System.Collections.ArrayList")
    Set Keys = CreateObject("System.Collections.ArrayList")
    
    Set NWords = CreateObject("System.Collections.ArrayList")
    NWords.Add "no"
    NWords.Add "not"
    Debug.Print NWords(0); NWords.Count
    
    Set KWords = CreateObject("System.Collections.ArrayList")
    KWords.Add "key1"
    KWords.Add "key2"
    KWords.Add "key3"
    KWords.Add "key4"
    KWords.Add "key5"
    Debug.Print KWords(3)

    For Each Rng In Selection
        With Application.WorksheetFunction
            Temp = Split(Rng)
            For i = 0 To UBound(Temp)
            
                For m = 0 To NWords.Count - 1
                    If Temp(i) = NWords(m) Then Negatives.Add i
                Next m
'----------------------------PROBLEM IS HERE------------------------------------------------------------                
                For j = 0 To KWords.Count - 1
                    If .Search(KWords(j), Temp(i)) Then Keys.Add i
                Next j
'----------------------------PROBLEM IS HERE------------------------------------------------------------                  
            Next i
            
            For k = 0 To Keys.Count - 1
                For n = 0 To Negatives.Count - 1
                Debug.Print "Key"; Keys(k); "negative"; Negatives(n)
                    Debug.Print "In Color Index"
                    If Abs(Negatives(n) - Keys(k)) < 5 Then Rng.Interior.ColorIndex = 35
                Next n
            Next k
            
        End With
    Next Rng
    
    Application.ScreenUpdating = True
End Sub

CodePudding user response：

Instead of using WorksheetFunction.Search, consider using InStr:

If InStr(1, Temp(i), KWords(j), vbTextCompare) > 0 Then