Excel VBA Insert rows based on input

April 14, 2018 at 11:29:18
Specs: Windows 7
I am trying to make an VBA formula to insert rows based on what sort of value is in a certain column. If a number is put in column B, I want it to automatically insert a row above and below. If the value put into column B contains the letter "P" I want to insert only one row, below the "P".
I'm not sure how to have it recognize when it is only a number, my current function is applying to any value inserted into the cell, not just numbers. This means that when I insert a "P" with a number, it is applying both the insert above and below, as well as inserting another row below.
Second of all, I currently have it recognizing only "P2", I'm not sure how to get it to recognize if the cell contains a P in addition to a number. ***EDIT** I just managed to fix this by using If Not Target.Value = "P2", so that is solved.

In addition, it would be great if it could only apply this when the value is inserted into a blank cell, and not apply if I'm editing a cell that already has a value.

Here is how I'm currently doing this:

Private Sub Worksheet_CHANGE(ByVal Target As Range)
On Error GoTo errHnd
'Don't do anything if more than one cell has been changed
If Target.Cells.Count > 1 Then Exit Sub
'CHECK CELL IS AN ADDED PART
If Target.Column = 2 Then
If Target = "P2" Then
'Disable events so code doesn't fire again when row is inserted
Application.EnableEvents = False
'INSERT ROW BELOW
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
End If
End If
'START OF CUE NUMBER INSERT
Application.EnableEvents = True
On Error GoTo errHnd
'Don't do anything if more than one cell has been changed
If Target.Cells.Count > 1 Then Exit Sub
'CHECK CELL IS A CUE NUMBER
If Target.Column = 2 Then
If Target.Value >= 0.001 Then

'Disable events so code doesn't fire again when row is inserted
Application.EnableEvents = False
'insert row below
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
'insert row above
ActiveCell.EntireRow.Insert Shift:=xlDown


End If
End If
errHnd:
'Re-enable event
Application.EnableEvents = True
End Sub


If anyone has any ideas about how to achieve these pieces, it would be great.

message edited by Stoli


See More: Excel VBA Insert rows based on input

Report •

#1
April 14, 2018 at 15:40:19
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •
Related Solutions


Ask Question