Insert row with specific values in columns after specific text

[chaostheory]

Hello guys,
I have an excel with hundres of rows similar to this one:

I was trying to find a way to find a specific "text", insert a blank row below it and fill it with specific values that I have set before.

For example: I want to find every row with "2b" and insert a row with specific values: column A: copy upper cell, column B: "new value #1", column C: "new value #2".

So before running this "script" I want to specify that I will get "rty" as "new value #1" and "4d" as "new value #2".

After running this my excel looks like this (inserted rows are green):

I was trying to find a solution to this, but every single one was only mentioning adding BLANK row, not filling this row up with specific values that I need.

Can somebody help? I have an excel with hundreds of lines that I need to repair :S

Best regards

 

[kweaver]

How about this?

Sub PromptInsert()
 Dim i As Long, lr As Long
 Dim in1 As Variant, in2 As Variant, in3 As Variant
 in1 = InputBox("After which?")
 in2 = InputBox("Value for B column")
 in3 = InputBox("Value for C column")
 
 lr = Worksheets("test").Cells(Rows.Count, "A").End(xlUp).Row
 For i = lr To 1 Step -1
  If Worksheets("test").Cells(i, 3) = in1 Then
  Worksheets("test").Rows(i + 1).Insert Shift:=xlShiftDown
  Worksheets("test").Cells(i + 1, 1) = Worksheets("test").Cells(i, 1).Value
  Worksheets("test").Cells(i + 1, 2) = in2
  Worksheets("test").Cells(i + 1, 3) = in3
  Else
  End If
Next i
End Sub

 

[chaostheory]

Hello,
thank you, but it gives me an error.

 

[kweaver]

Hmm. I used this:

FindCell_PrintPDF.xlsm
	A	B	C
1	A	aaa	11b
2	A	bbb	2b
3	A	ccc	3c
4	B	ddd	9b
5	B	eee	10b
6	B	fff	11b
7	B	ggg	12b
8	C	hhh	99c
9	C	jjj	101c
10	C	kkk	102c
11	C	mmm	11b
12	D	nnn	99d
13	D	ppp	100D
test

And picked 11b and inserted aaaaa and bbbbb

FindCell_PrintPDF.xlsm
	A	B	C
1	A	aaa	11b
2	A	aaaaa	bbbbb
3	A	bbb	2b
4	A	ccc	3c
5	B	ddd	9b
6	B	eee	10b
7	B	fff	11b
8	B	aaaaa	bbbbb
9	B	ggg	12b
10	C	hhh	99c
11	C	jjj	101c
12	C	kkk	102c
13	C	mmm	11b
14	C	aaaaa	bbbbb
15	D	nnn	99d
16	D	ppp	100D
test

 

[chaostheory]

How about this?

Sub PromptInsert()
Dim i As Long, lr As Long
Dim in1 As Variant, in2 As Variant, in3 As Variant
in1 = InputBox("After which?")
in2 = InputBox("Value for B column")
in3 = InputBox("Value for C column")

lr = Worksheets("test").Cells(Rows.Count, "A").End(xlUp).Row
For i = lr To 1 Step -1
  If Worksheets("test").Cells(i, 3) = in1 Then
  Worksheets("test").Rows(i + 1).Insert Shift:=xlShiftDown
  Worksheets("test").Cells(i + 1, 1) = Worksheets("test").Cells(i, 1).Value
  Worksheets("test").Cells(i + 1, 2) = in2
  Worksheets("test").Cells(i + 1, 3) = in3
  Else
  End If
Next i
End Sub

Sorry for previous post. I made a mistake with worksheet name. This works perfectly! Thank you.