Excel VBA Macros

[mrob]

Hi All,

I'm writing this macros and could use some help. So far the macro finds a word in the spreadsheet and then adjacent word. For example:

"The building has a broken pipe beneath the first floor."

1.Find Text “broken”
2.Find text “broken” and adjacent word “pipe”

To accomplish this task I parsed the words into separate cells and then find the word “broken” then do a “find next” in the adjacent cell using findoffset in my code. I added an error handler if a word is not in the spreadsheet.


What I need help doing next is:

1. Define adjacent as words show up as before/after- meaning, treat "pipe broken" and "broken pipe" the same. I'm guessing that would be the use of some previous or next code?


2. My error handler doesn't exclude NULLS in a paragraph. I would need to handle NULLS in a paragraph so that it doesn't show the count of NULLS.The null situation can happen if/when the word of interest "example broken" is at end of a paragraph or a document .

3. I'm guessing I will need a RegExp to handle ignoring common words such as "a", "an", "if", "and", "or" etc. For example, I do not want to find "broken" and adjacent word "a" , "an", "if", "and", "or" etc.

This is my macro:

Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String


Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "broken"
Set match = ws.Cells.Find(findMe)

If (Not match Is Nothing) Then

findOffset = match.Offset(, 1).Value
Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & findOffset & """,A1:AZ96,1)))))")
MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """. The word """ & findOffset & """ is found """ & Number & """ times!"

Else
'not match
MsgBox "Sorry the text was not found please try again. Macro stopping"

End If

End Sub

Thanks for your help in advance!

 

[Tweak155]

Whoa, not sure what you're trying to do with that Evaluate line... but here is a way to find adjacent words that aren't a list of words (NOTE: There are better ways to do this if you have a large list of words):

Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String
'Added
Dim offsetCol as Long, tempStr as String
Dim searchDirection as Long

searchDirection = xlRight 'change this to xlLeft to go the other way

Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "broken" 
Set match = ws.Cells.Find(findMe)

If (Not match Is Nothing) Then
'added code

If searchDirection = xlRight Then
    offsetCol = 1
Else
    offsetCol = -1
End If

tempStr  = LCase(Trim(match.Offset(, offsetCol).Value))

While match.Offset(, offsetCol).Column >= 1 And tempStr = "a" And tempStr = "an" 'add all the words you want to skip here, lower case
    If searchDirection = xlRight Then
        offsetCol = offsetCol + 1
    Else
        offsetCol = offsetCol - 1
    End If
    tempStr =  LCase(Trim(match.Offset(, offsetCol).Value))
Wend

findOffset = match.Offset(, offsetCol).Value

Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & findOffset & """,A1:AZ96,1)))))")
MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """. The word """ & findOffset & """ is found """ & Number & """ times!"

Else
'not match
MsgBox "Sorry the text was not found please try again. Macro stopping"

End If

End Sub

 

[Tweak155]

I think a better way to do your word count is the following (I'm assuming this is what you are doing with the Evaluate line...):

Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String
'Added
Dim offsetCol as Long, tempStr as String
Dim searchDirection as Long
Dim tempCell as Range

searchDirection = xlRight 'change this to xlLeft to go the other way

Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "broken" 
Set match = ws.Cells.Find(findMe)

If (Not match Is Nothing) Then
'added code

If searchDirection = xlRight Then
    offsetCol = 1
Else
    offsetCol = -1
End If

tempStr  = LCase(Trim(match.Offset(, offsetCol).Value))

While match.Offset(, offsetCol).Column >= 1 And (tempStr = "a" or tempStr = "an") 'add all the words you want to skip here, lower case
    If searchDirection = xlRight Then
        offsetCol = offsetCol + 1
    Else
        offsetCol = offsetCol - 1
    End If
    tempStr =  LCase(Trim(match.Offset(, offsetCol).Value))
Wend

findOffset = match.Offset(, offsetCol).Value

'Start word count code
Number = 0

For Each tempCell in ws.Cells.SpecialCells(xlCellTypeConstants)
    If LCase(Trim(tempCell.Value)) = tempStr Then
        Number = Number + 1
    End If
Next tempCell

MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """. The word """ & findOffset & """ is found """ & Number & """ times!"

Else
'not match
MsgBox "Sorry the text was not found please try again. Macro stopping"

End If

End Sub

 

[mrob]

Thanks for your reply. However, the macro is still finding the unwanted words like "a" in the adjacent cell.

 

[Tweak155]

Thanks for your reply. However, the macro is still finding the unwanted words like "a" in the adjacent cell.

Sorry used an "And" when I should have used an "or". Try it now from my 2nd reply.

 

[mrob]

Thank you that worked.

 

[mrob]

[FONT=&quot]If I wanted to search the next line of text should I add a loop to start a new search for the next word? [/FONT]
[FONT=&quot]For example, the sentence is:

The HQ building has a broken pipe underneath the first floor. The main building has a broken switch in the hallway on the second floor. The ladies restroom has a broken faucet.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]How do I keep searching a large spreadsheet? So far it only finds the first word "broken" I want it to find all words that are broken:

The msgbox would look something like:[/FONT]
[FONT=&quot]
"The adjacent word to "broken" is "pipe" . The word "pipe" is found "10" times!"
"The adjacent word to "broken" is "switch". The word "switch" is found "15" times!"
"The adjacent word to "broken" is "faucet". The word "faucet" is found "2" times!"

[/FONT]

 

[Tweak155]

[FONT=&quot]If I wanted to search the next line of text should I add a loop to start a new search for the next word? [/FONT]
[FONT=&quot]For example, the sentence is:

The HQ building has a broken pipe underneath the first floor. The main building has a broken switch in the hallway on the second floor. The ladies restroom has a broken faucet.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]How do I keep searching a large spreadsheet? So far it only finds the first word "broken" I want it to find all words that are broken:

The msgbox would look something like:[/FONT]
[FONT=&quot]
"The adjacent word to "broken" is "pipe" . The word "pipe" is found "10" times!"
"The adjacent word to "broken" is "switch". The word "switch" is found "15" times!"
"The adjacent word to "broken" is "faucet". The word "faucet" is found "2" times!"

[/FONT]

I'm not sure why you would do this, if you find the word "pipe" after "broken" for example, then search for the word "pipe" only, you only know how many times "pipe" occurs. Wouldn't you want to know how many broken pipes there are, and therefor should find the combination of the words "broken pipe" and "pipe broken" etc? In other words, what if one of the other sentences said the following:

"The pipe seems to be fine, but the valve at the end appears to not be working"

EDIT:

Better said, the english language is very hard to parse with a program. Unless this is a homework assignment, you're probably better off looking for general terms such as only "broken" instead of combination of terms which can create a very large list I.E broken pipe, broken valve, broken window, etc.

Rank your terms and find the most important first. For example, I would rank "broken" higher than I would rate "worn".

EDIT EDIT:

Since you got me thinking, what about auto separating sentences by keywords into different tabs. I.E you look for a sentence with the word "broken" and that goes to a tab called "Broken".

EDIT EDIT EDIT:

Then you'd need a ranking system because what if a sentence has two key words? Probably doesn't make sense to put it into two tabs, lol.

What exactly is the goal here?

 

[mrob]

Excellent observation and I understand your logic but for now I just want it to find this criteria:

The msgbox would look something like:

"The adjacent word to "broken" is "pipe" . The word "pipe" is found "10" times!"
"The adjacent word to "broken" is "switch". The word "switch" is found "15" times!"
"The adjacent word to "broken" is "faucet". The word "faucet" is found "2" times!"

This would make more sense if I was searching for a fname and lname like "John Doe".

So far I have a separate macro that will tell me how many times the word "broken" appears. That was the line with Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & str & """,A1:AZ96,1)))))")

The next step I wanted to take was to find the word "broken" and the adjacent "word" in the next cell with how many times the "word" in the adjacent cell appears. I also want to keep searching for the word "broken" to display the msgbox with:

"The adjacent word to "broken" is "pipe" . The word "pipe" is found "10" times!"
"The adjacent word to "broken" is "switch". The word "switch" is found "15" times!"
"The adjacent word to "broken" is "faucet". The word "faucet" is found "2" times!"

 

[Tweak155]

Excellent observation and I understand your logic but for now I just want it to find this criteria:

The msgbox would look something like:

"The adjacent word to "broken" is "pipe" . The word "pipe" is found "10" times!"
"The adjacent word to "broken" is "switch". The word "switch" is found "15" times!"
"The adjacent word to "broken" is "faucet". The word "faucet" is found "2" times!"

This would make more sense if I was searching for a fname and lname like "John Doe".

So far I have a separate macro that will tell me how many times the word "broken" appears. That was the line with Number = Evaluate("=SUMPRODUCT(--(NOT(ISERROR(FIND(""" & str & """,A1:AZ96,1)))))")

The next step I wanted to take was to find the word "broken" and the adjacent "word" in the next cell with how many times the "word" in the adjacent cell appears. I also want to keep searching for the word "broken" to display the msgbox with:

"The adjacent word to "broken" is "pipe" . The word "pipe" is found "10" times!"
"The adjacent word to "broken" is "switch". The word "switch" is found "15" times!"
"The adjacent word to "broken" is "faucet". The word "faucet" is found "2" times!"

Okay, should only require a small modification. Give me a min.

 

[Tweak155]

Try this:

Sub Module6()
'
'FindPlusOffset&Count
'
'
Dim ws As Worksheet
Dim match As Range
Dim findMe As String
Dim findOffset As String
'Added
Dim offsetCol As Long, tempStr As String
Dim searchDirection As Long
Dim tempCell As Range
Dim searchCell As Range
Dim foundWord As Boolean
Dim wordRange As Range, printStr As String

foundWord = False

searchDirection = xlRight 'change this to xlLeft to go the other way

Set ws = ThisWorkbook.Sheets("Sheet1")
findMe = "broken"
'Set match = ws.Cells.Find(findMe)

'If (Not match Is Nothing) Then
'added code
Set wordRange = ws.Cells.SpecialCells(xlCellTypeConstants)

printStr = ""
For Each searchCell In wordRange
    
    tempStr = LCase(Trim(searchCell.Value)) 'LCase(Trim(match.Offset(, offsetCol).Value))
    
    If tempStr = findMe Then
        If searchDirection = xlRight Then
            offsetCol = 1
        Else
            offsetCol = -1
        End If
        
        foundWord = True
        Set match = searchCell
        While match.Offset(, offsetCol).Column >= 1 And (tempStr = "a" Or tempStr = "an") 'add all the words you want to skip here, lower case
            
            If searchDirection = xlRight Then
                offsetCol = offsetCol + 1
            Else
                offsetCol = offsetCol - 1
            End If
            tempStr = LCase(Trim(match.Offset(, offsetCol).Value))
        Wend
        
        findOffset = match.Offset(, offsetCol).Value
        
        'Start word count code
        Number = 0
        
        For Each tempCell In wordRange
            If LCase(Trim(tempCell.Value)) = findOffset Then
                Number = Number + 1
            End If
        Next tempCell
    
        printStr = printStr & "The adjacent word to """ & findMe & """ is """ & findOffset & """. The word """ & findOffset & """ is found """ & Number & """ times!" & vbNewLine
    End If
Next searchCell
'Else
'not match

If Not foundWord Then
    MsgBox "Sorry the text was not found please try again. Macro stopping"
Else
    MsgBox printStr
End If

End Sub

 

[mrob]

Yes excellent! that's what I needed. Thank you...

 

[Tweak155]

Yes excellent! that's what I needed. Thank you...

No problem, but keep in mind there is a fault in your search.

In your example, the third sentence ends in "broken faucet.". Therefor the search term is looking for "faucet." with the period.

I'd recommend removing all punctuation marks from all the search terms, but it seems you need a specific purpose. Also the logic will repeatedly find the same search terms. I.E if "broken pipe" is on line 1 and 10, it will look for "pipe" when it hits both lines and associate it with the word "broken", thus printing the information twice.

All this can be avoided, of course

Depends on your needs - it would take some setup to avoid printing it more than once, and likely isn't worth it. But removing the punctuation could be useful.

 

[mrob]

Yes I understand your points...this is a work in progress...the end is unknown at this time LOL. I may have to revisit this thread