CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
mgrobins
mgrobins

Reputation: 37

VBA Excel Macro find text and find next adjacent cell

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

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

Upvotes: 1

Views: 17927

Answers (1)

Jon Crowell
Jon Crowell

Reputation: 22340

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.

Upvotes: 2

Related Questions