VBA Excel Macro find text and find next adjacent cell

Question

I'm trying to write a VBA Excel Macro to create an algorithm that will find text and find next adjacent cell to display text in a msgbox for example:

I parsed the sentence "The building has a broken pipe beneath the first floor." into separate cells. Using this code:

Sub Module3()
'
' Parse text
'

'
    Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
End Sub

The sentence is now parsed. Once I select a cell how can I display the adjacent cell in a msgbox

Find Text “broken”
Find text “broken” and adjacent word “pipe”

Jon Crowell Jon Crowell · Accepted Answer · 2013-07-01T19:52:57

This will do what you need:

Sub FindPlusOffset()
    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)
    findOffset = match.Offset(, 1).Value
    MsgBox "The adjacent word to """ & findMe & """ is """ & findOffset & """."
End Sub

You'll probably want to add some error handling in case the word you are looking for isn't found. You can set the ws object to whatever sheet name you are working with.

VBA Excel Macro find text and find next adjacent cell

1 Answers