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
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