Find Text Within A String

[gtbreps_2]

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?

[GTO]

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:

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

[gtbreps_2]

GTO,
That worked perfect. Thank you.

[GTO]

You are most welcome and welcome to vbaexpress :-)

[snb]

alternatively written:

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