Solved: Insert Row after Specific Text

Hello,

I am using Excel 2010 and trying to insert a row after the word Total appears in any row in column A. Sometimes other words will be in column A with the word Total, and I am trying to create a loop that will

1) Find Total when it appears in Column A, even if other words are in the cell.
2) Insert a row beneath the row that has the word Total in it
3) Bold the word Total

I have spent the past few days trying to work through what's online and have had no success. I'd appreciate any help!

Greetings Zuser,

Welcome to vbaexpress!:hi:

Try:
[VBA]Option Explicit

Sub exa()
Dim REX As Object '<-- RegExp
Dim lLRow As Long
Dim i As Long

'// Set a reference to Regular Expressions and a simple pattern simply requiring it //
'// to find the word with word boundaries leading/following. If you find you need //
'// to include stuff like "totals", "totalling", etc - the pattern would need changed//
Set REX = CreateObject("VBScript.RegExp")
With REX
.Pattern = "\btotal\b"
.Global = False
.IgnoreCase = True
End With

With Sheet1 '<-- codename or sheet/tab name -->ThisWorkbook.Worksheets ("Sheet1")

lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For i = lLRow To 1 Step -1
If REX.Test(.Cells(i, 1).Value) Then
.Cells(i, 1).Characters(Start:=REX.Execute(.Cells(i, 1).Value)(0).FirstIndex + 1, _
Length:=5) _
.Font.FontStyle = "Bold"

.Rows(i + 1).Insert Shift:=xlDown
End If
Next
End With
End Sub[/VBA]

Hope that helps,

Mark

Thanks GTO,

That works perfectly, I can't thank you enough.

Very happy I joined this board.

Quote:

---

Originally Posted by Zuser83970

Thanks GTO,

That works perfectly, I can't thank you enough.

Very happy I joined this board.

---

:friends: Glad that worked and welcome aboard.

Welcome to the VBAX forum Zuser83970. As Mark has suggested (within the code) the logic of this comes from working from the bottom of the column and upwards.