Solved: Insert Row after Specific Text

[Zuser83970]

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!

[GTO]

Greetings Zuser,

Welcome to vbaexpress!

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

[Zuser83970]

Thanks GTO,

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

Very happy I joined this board.

[GTO]

Thanks GTO,

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

Very happy I joined this board.

Glad that worked and welcome aboard.

[Aussiebear]

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.