VBA - Finding a value in column A, selecting that row, pasting it above with formulae

[xMesa]

Hello, i'm fairly new to VBA... doing a user interface for a excel worksheet at work. I have some background in webdesign and i thought this would be easy...

Anyway what i'm trying to do is:

I am trying to find ~NP~ in column A.
Selecting the entire row where ~NP~ is found.
Copying it. Pasting it 1 row above, including formulae.
Selecting newly pasted row. Deleting ~NP~ so it won't find it on next search.

I made the userform and everything but i struggle with the code... i tried different bits and pieces of code but couldn't make em work together.

At this point i am at:

Private Sub CommandButton1_Click()


'find ~NP~ in AA

    Columns("A:A").Select
        Set cell = Selection.Find(What:="NP", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False)
    
    If cell Is Nothing Then
        MsgBox "~NP~ inexistent"
    Else
        cell.EntireRow.Activate

'copy and paste it above

        Dim iRow As Integer
        iRow = Selection.Row
        Selection.EntireRow.Select
        Selection.Insert Shift:=xlDown
        Rows(iRow + 1).Copy
        Rows(iRow).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

End If
End Sub

[mana]

Sub test()
    Dim i As Long
    
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Range("A" & i).Value = "NP" Then
            Range("A" & i).ClearContents
            Rows(i).Copy
            Rows(i).Insert
        End If
    Next
    Application.CutCopyMode = False
   
End Sub

[xMesa]

Wow. i feel stupid it works, but it deletes all NP text from column A... so it doesn't work a second time.
Also it copies it BELOW the old NP, not ABOVE

Thank you very much for the reply.

EDIT: i think it's in the order of these operations. Copy then insert above then clear contents? i'll try this

Range("A" & i).ClearContents
Rows(i).Copy
Rows(i).Insert

EDIT2:

Yes it worked now, by swapping

Range("A" & i).ClearContents

after insert

Now i need to set the new line as active row and input the data from my userform.

[xMesa]

Private Sub NPaddlinebutton_Click()
   
    Dim i As Long
     
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Range("A" & i).Value = "~NP~" Then
            Rows(i).Copy
            Rows(i).Insert
            Range("A" & i).ClearContents
            Rows(i).Activate
        End If
    Next
    Application.CutCopyMode = False
    
    Cells(ActiveRow, 2).Value = NPclient.Value
    Cells(ActiveRow, 7).Value = NPdata.Value
    Cells(ActiveRow, 8).Value = NPdescriere.Value
    Cells(ActiveRow, 9).Value = NPcontact.Value
    Cells(ActiveRow, 10).Value = NPore.Value
    Cells(ActiveRow, M).Value = NPdelay.Value
    Cells(ActiveRow, AA).Value = NPnumeutilizator.Value
    Cells(ActiveRow, AB).Value = NPstatus.Value


End Sub

I get a error on ActiveRow... it doesn't see the new inserted row as a active row so i can input the data from the userform

Either i need to replace Cells(ActiveRow, ...) with something else or Rows(i).Activate doesn't work like i think it does.


Any tips?

[mana]

    Dim ws As Worksheet
    Dim m
     
    With Worksheets("Sheet1")
        m = Application.Match("~NP~", .Columns("A"), 0)
        If IsError(m) Then Exit Sub
        
        .Rows(m).Copy
        .Rows(m).Insert
        
        .Cells(m, "A").ClearContents
        .Cells(m, "B").Value = NPclient.Value
        .Cells(m, "G").Value = NPdata.Value
        .Cells(m, "H").Value = NPdescriere.Value
        .Cells(m, "I").Value = NPcontact.Value
        .Cells(m, "J").Value = NPore.Value
        .Cells(m, "M").Value = NPdelay.Value
        .Cells(m, "AA").Value = NPnumeutilizator.Value
        .Cells(m, "AB").Value = NPstatus.Value
        
    End With