View Full Version : [SOLVED] Find Text Within A String

01-03-2017, 07:13 AM
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?

01-03-2017, 07:45 AM
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,


01-03-2017, 08:11 AM
That worked perfect. Thank you.

01-03-2017, 09:16 AM
You are most welcome and welcome to vbaexpress :-)

01-04-2017, 01:51 AM
alternatively written:

Sub M_snb()
on error resume next
End Sub