Insert X number of rows based on variable text string search and input text

dubmartian

New Member
Joined
Dec 16, 2016
Messages
20
I would like to edit this script to add text to inserted rows.
Every insert row will be 2 and I would like to add text to the two cells.

example:

apple
pear
bananna
(scripts inserts 2 rows after every occasion of bananna)
inserted row 1 ( text string included = "help")
inserted row 2 (text string included = "me")
peach
strawberry

end example

Can this be done?

The code Im using ->

Option Explicit


Sub Insert_Rows()

Dim i As Long, lRows As Long, lastrow As Long, lngCount As Long
Dim strTxt As String

Application.ScreenUpdating = False

lastrow = Cells(Rows.Count, "C").End(xlUp).Row

lRows = Application.InputBox("How many rows do you want to insert?", Type:=1)

If lRows < 1 Then
MsgBox " You must enter a number greater than zero"
Exit Sub
End If

strTxt = Application.InputBox("Enter the text string to search on. Rows will be inserted below each cell containing this string.")

If Len(strTxt) < 1 Then
MsgBox "You must enter a text string consisting of at least one character"
Exit Sub
End If

With ActiveSheet

lngCount = Application.WorksheetFunction.CountIf(.Range("C1:C" & lastrow), strTxt)

If lngCount < 1 Then
MsgBox "The text string you entered is not listed - cancelling", vbExclamation
Exit Sub
End If

On Error Resume Next

For i = lastrow To 1 Step -1
If .Cells(i, 3).Value = strTxt Then
.Range("C" & i + 1 & ":C" & i + lRows).Insert shift:=xlDown
End If
Next i

End With

Application.ScreenUpdating = True


End Sub
 
Glad to help & thanks for the feedback
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,215,020
Messages
6,122,712
Members
449,093
Latest member
Mnur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top