[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Targeting occurences of two non-contiguous words with InStr

Posted on 2011-05-05
16
Medium Priority
?
282 Views
Last Modified: 2012-05-11
Pls take a look at the attached workbook. I have a very simple but powerful code that finds matches in a list using InStr only. Is it possible to have it find instances of two words together but not necessarily contiguous?

Thanks,
John
Non-contiguous-Keywords.xls
0
Comment
Question by:gabrielPennyback
  • 7
  • 5
  • 3
15 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35703917
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35703920
sorry...replace this line:

 strWords = split(UCase(cel))

with this:

 strWords = split(UCase(cel), " ")
0
 
LVL 6

Accepted Solution

by:
theKashyap earned 1000 total points
ID: 35704152
One in post 35703917 is not correct, it would set j = INOP even when it finds just one of the words. E.g. if some cel is "it is working" it'll still be set to INOP.

Correct one (and many many times faster) should be this:
Sub Code_INOP_Test333()
    Dim cel As Range, j As Range
    Dim kw_array As New Collection
    Dim matched As Boolean
    Dim cell_val As String
    
    For Each i In [KW_INOP]
        kw_array.Add Split(i.Value, " ")
    Next
    
    For Each cel In Range("A1:A8")
        cell_val = UCase(cel.Value)
        Dim iWordCntr As Integer
        For Each strWords In kw_array
            matched = True
            For Each myword In strWords
                If InStr(cell_val, UCase(myword)) = 0 Then
                    matched = False
                    Exit For
                End If
            Next
            If matched Then
                cel.Offset(0, 8) = "INOP"
                Exit For
            End If
       Next
    Next
End Sub

Open in new window


Can be further enhanced to look for words in correct sequence as well if needed. This one sets j to INOP even for "working not". Let me know if you need the sequencing.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:theKashyap
ID: 35704155
Small change Line 11 should be:     "For Each cel In [All_Incidents]"
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35704175
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
0
 
LVL 6

Expert Comment

by:theKashyap
ID: 35704331
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].
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35706562
I think you're right on both accounts...I'll adjust if gabriel wants to explore my code further and needs help making adjustments.
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35708345
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

0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 1000 total points
ID: 35711058
OK, I had to tweak your/my code a bit, but I think this is very close to what you want:

 
Sub Code_INOP_Test333()
    Dim celTest As Range
    Dim celMatch As Range
    Dim celDestination As Range
    Dim iRow As Long
    Dim strWords() As String
    Dim iWordCntr As Integer
    Dim bMatchAll As Boolean
    
    For Each celTest In [All_Incidents]
        celTest.Select
        Set celDestination = celTest.Offset(0, 8)
        For Each celMatch In [KW_INOP]
            strWords = Split(UCase(celMatch.Value), " ")
            bMatchAll = True
            For iWordCntr = 0 To UBound(strWords)
                If InStr(1, celTest.Value, strWords(iWordCntr), VbCompareMethod.vbTextCompare) = 0 Then
                    bMatchAll = False
                    Exit For
                End If
            Next
            If bMatchAll Then
                celDestination.Value = "INOP"
            End If
       Next
    Next
End Sub

Open in new window

0
 
LVL 6

Expert Comment

by:theKashyap
ID: 35717606
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

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35774855
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 36000394
Q 35711058 seemed to cover it.  Not sure if there was anything else needed.
0
 
LVL 6

Expert Comment

by:theKashyap
ID: 36000968
I believe 35704152 is more optimal and correct, but thought the OP would decide which one to go with.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 36003727
ah, maybe I misinterpreted the author's comment about "It seems to work correctly."
I guess we should just split the points evenly.
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 36142608
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question