Add row above based on specific cell criteria.

[Chad8]

I've hit a wall it seems I am wondering if anyone can help meet out. I'm building a Excel macro but stuck on how to accomplish the following procedure.

Step 1: If any cell(s) in “J” column is greater than or equal to the value in Cell M4, Insert 1 new row above all cells that meet the criteria.

Step 2: Copy and move all cells that meet criteria in step 1: up 1 and to the right 1 (paste in new row). Then enter 0 in the in the original cell that it was copied and moved from.

<strike></strike>
I would post a screenshot to better explain however this site is not exactly newbie friendly I see.

 

[RCBricker]

I've hit a wall it seems I am wondering if anyone can help meet out. I'm building a Excel macro but stuck on how to accomplish the following procedure.

Step 1: If any cell(s) in “J” column is greater than or equal to the value in Cell M4, Insert 1 new row above all cells that meet the criteria.

Step 2: Copy and move all cells that meet criteria in step 1: up 1 and to the right 1 (paste in new row). Then enter 0 in the in the original cell that it was copied and moved from.

<strike></strike>
I would post a screenshot to better explain however this site is not exactly newbie friendly I see.

try this

****** NOTE change the set ws=sheets("Chad8") to your sheet name

Sub Chad8()
Dim ws As Worksheet
Dim lngrow As Long, lnghead As Long
Dim rng As Range
Dim i As Variant, j As Variant

    Set ws = Sheets("Chad8")
    With ws
        j = ws.Range("M4").Value
        lnghead = ws.Range("J" & ws.Rows.Count).End(xlUp).End(xlUp).Row
        lngrow = cells.Find(What:="*", _
            after:=cells(1), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row

        For i = lngrow To lnghead Step -1
            Set rng = ws.cells(i, 10)
            rng.Select
            If rng.Value > j Then
                rng.EntireRow.Insert
                rng.Offset(-1, 1).Value = rng.Value
                rng.Value = 0
            End If
        Next
    End With
End Sub

 

[Chad8]

Thanks RCbricker you are most helpful. The code worked with just a couple minor bugs.

1. the cells that contain values equal to the value in cell "M4" did not transfer over.
2. the 'J' column title in cell J1 changes from "SD" and gets transferred over. I would prefer the title to remain as a title.

Glad I found this site. Learning VBA is like learning a new language but something I am very interested in doing.

 

[Chad8]

Thanks RCbricker you are most helpful. The code worked with just a couple minor bugs.

1. the cells that contain values equal to the value in cell "M4" did not transfer over.
2. the 'J' column title in cell J1 changes from "SD" and gets transferred over. I would prefer the title to remain as a title.

Glad I found this site. Learning VBA is like learning a new language but something I am very interested in doing.

Anyone?