Targeting occurences of two non-contiguous words with InStr

How about this?


Sub Code_INOP_Test222()
Dim cel As Range, j As Range
For Each cel In [All_Incidents]
cel.Select
    Set j = cel.Offset(0, 8)
    Dim i As Long
    DIM strWords() as string
    DIM iWordCntr as Integer
    For i = [KW_INOP].Row To [KW_INOP].End(xlDown).Row
         strWords = split(UCase(cel))
      For iWordCntr=0 to UBOUND(strWords)
       If InStr(strWords(iWordCntr), Cells((i), [KW_INOP].Column)) > 0 Then j = "INOP"
      Next
   Next
Next
End Sub

And realize that it will fine things that are NOT entire words also, such as KNOT

sorry...replace this line:

 strWords = split(UCase(cel))

with this:

 strWords = split(UCase(cel), " ")

Small change Line 11 should be:     "For Each cel In [All_Incidents]"

Oh, was it supposed to check for BOTH words?  If so, you're correct Kashyap.

To match on both, this would be better:

Sub Code_INOP_Test222()
Dim cel As Range, j As Range
For Each cel In [All_Incidents]
cel.Select
    Set j = cel.Offset(0, 8)
    Dim i As Long
    DIM strWords() as string
    DIM iWordCntr as Integer
    DIM bMatchAll as Boolean
    For i = [KW_INOP].Row To [KW_INOP].End(xlDown).Row
         strWords = split(UCase(cel))
         bMatchAll = True
      For iWordCntr=0 to UBOUND(strWords)
       If InStr(strWords(iWordCntr), Cells((i), [KW_INOP].Column)) = 0 Then
           bMatchAll=False
           Exit For
       End If
      Next
     If bMatchAll Then
           j = "INOP"
     End If
   Next
Next
End Sub

was it supposed to check for BOTH words?

Don't know. Not specified in the problem stmt.  But it seemed logical that "working" and "not working" shouldn't both be INOP. :-)
PS: The splitting of words in [KW_INOP] needn't be done for each word in [All_Incidents].

I think you're right on both accounts...I'll adjust if gabriel wants to explore my code further and needs help making adjustments.

theKashyap, your answer ID:35704152 succeeds in getting the cells where the "not" and the "working" are separated by other words ( such as "not always working"), but it also returns false positives. I eventually discovered that I get an "INOP" 8 cells to the right of any record which has a word containing an 'o', an 'n' and a 't' in any order. For example "station" or control" I haven't gone through all 465 rows to see if that is always the case, but I'm guessing it.

Can you see anything in your code that causes that, and how do I modify the code so that doesn't happen?

rspahitz, I'm not sure if your code is getting all the right ones, but I'll test it some more later.

Thanks,
John

Hmm.. I tested again.. It seems to work correctly. I.e. it does not match station of control.
Can you post the code you used along with the set of input?
Here is the output (match = INOP, No match = no match):
SR/NR                                                Match
INOP                                                Match
does not seem to be working                        Match
station                                                No match
control                                                No match
station control                                          No match
/NR                                                      Match
/ NR                                                      Match
NOT WORKING                                    Match
NO POWER                                          Match
BLANK                                                Match
NOT                                                 No match
WORKING                                          No match

Long weekend, sorry! I've had a bunch of other issues take precedence at work but i really want to get this nailed down as soon as I can. Thanks for your patience/

John

Q 35711058 seemed to cover it.  Not sure if there was anything else needed.

I believe 35704152 is more optimal and correct, but thought the OP would decide which one to go with.

ah, maybe I misinterpreted the author's comment about "It seems to work correctly."
I guess we should just split the points evenly.

I'm so sorry for being AWOL again. To be honest I've been re-routed to other projects for a while but when I get back to this one I'm sure one or both of your answers will work for me.

Thank you for your patience!

- John