PDA

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



xMesa
09-14-2016, 01:22 AM
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
09-14-2016, 04:12 AM
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
09-14-2016, 04:23 AM
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
09-15-2016, 11:17 PM
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
09-16-2016, 04:35 AM
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