Find Text Within A String

I am trying to have VBA search through column C and find the 1st instance of the word "Option" in a cell. The cell "Option" exists in will have multiple words. Once VBA finds the word "Option" I want it to insert 2 rows above that row and exit the sub.

I've tried using InStr but the only references I can find make me thing I have to input a specific string a my main string. I'd assume I need to combine a for...then or do...loop with an InStr or a Find function. But I don't know how to have them just look in the cell and then perform a function if the word "Option" exists.

Any help?

The .Find method will find partial matches (one word in a group of words for instance); the one caveat is that it will find 'Options' as well as 'Option'. If that is not a concern, something like:

Code:

---

Option Explicit
 
Sub example()
Dim rngLookIn As Range
 
  'Note: "Sheet1" refers to the sheet's CodeName, not the name on the sheet's tab.
  With Sheet1
    Set rngLookIn = .Range("C:C").Find(What:="option", _
                                      After:=.Cells(.Rows.Count, "C"), _
                                      LookIn:=xlValues, _
                                      LookAt:=xlPart, _
                                      SearchOrder:=xlByRows, _
                                      SearchDirection:=xlNext, _
                                      MatchCase:=False _
                                      )
 
    If Not rngLookIn Is Nothing Then
      'insert rows
      .Rows(CStr(rngLookIn.Row) & ":" & CStr(rngLookIn.Row + 1)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
  End With
 
End Sub

---

Hope that helps,

Mark

GTO,
That worked perfect. Thank you.

You are most welcome and welcome to vbaexpress :-)

alternatively written:

Code:

---

Sub M_snb()
  on error resume next
  Sheet1.Columns(3).Find("option").Resize(2).EntireRow.Insert
End Sub

---