Insert new ROW if cell equals value

[Jeremn]

Any guidance would be greatly appreciated. I’m trying insert a row if a cell
value is equal to a number, and then copy that value to a cell in the new row
.. For example: if A1=â€x†then insert a row between row 1 and 2 and copy the
value of A1 into B2 of the inserted row. Thank you very much whoever can
help!

A B C D
1 x datae .25 0
<Insert new row here>
2 stuff moredata .20 2.1
3 other pcs 2.1 0

I’ve got this code thus far:

Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) = LCase("x") Then
.Rows(iRow + 1).EntireRow.Insert
End If
Next iRow
End With

 

[Mike H]

Hi,

Inserting the rows is explained well enough but I'm confused about what and
where to paste. Try this for a start:-

Sub inserrows()
x = 9
mycolumn = "A"
For i = Cells(Rows.Count, mycolumn).End(xlUp).Row To 1 Step -1
If Cells(i, mycolumn) = 9 Then
Rows(i + 1).Insert
Cells(i, mycolumn).Offset(1, 1).Value = x
End If
Next i
End Sub

 

[Jeremn]

Hi Mike,

Thank you for your reply, let me try and explaine again. Now that i've
inserted a row based on a specific value in column A, how can I copy that
vaule into the new row in column B? Does this make sense?

Thanks again for your help!!!

 

[Jeremn]

Sorry Mike,

That worked perfectly!!

Hi Mike,

Thank you for your reply, let me try and explaine again. Now that i've
inserted a row based on a specific value in column A, how can I copy that
vaule into the new row in column B? Does this make sense?

Thanks again for your help!!!

 

[Jeremn]

How do I set "X" to any value greater than 0? I tried x = not null and X > 0
but neither worked???

 

[Mike H]

Hi,

Not tested but this should do it

Sub inserrows()
x = 9
mycolumn = "A"
For i = Cells(Rows.Count, mycolumn).End(xlUp).Row To 1 Step -1
If Cells(i, mycolumn) > 0 Then
Rows(i + 1).Insert
Cells(i, mycolumn).Offset(1, 1).Value = Cells(i, mycolumn).value
End If
Next i
End Sub

Mike

 

[Jeremn]

Hey Mike,

Thanks again for your wisdom! It's very much appreciated. Happy Holidays.

Regards,

JereH