Finding a Row and cut and copy

T

Taner Kalkay

I was not able to solve my problem yet. I am trying to find a ro
carrying certain data in a page and I want to cut this row and paste t
another page.

VLOOKUP finds any data at the search column and and copies the indexe
value found at the place where the VLOOKUP required.

With this I can copy all the row that found data exist. But my proble
is also to delete the contents of the copied row or indidually th
cells at their original place.

When I use FIND Function it behaves similar to VLOOKUP and finds an
copies the data where it is required, When I use the Search under th
File Tab in Excell it finds and shows the row that I want to operate
This is cool but it requires the search data to be typed (no cut an
paste allowed). But I dont want to type it because I want to use thi
operation, finding, cutting and pasting in a macro.

Do I want something impossible ?

If anyone can help I will be greatfull.

Taner Kalka
 
V

Vaughan

Hi Taner

I don't think you are going to get a formula to cut and copy. You can get a construction that will return your data as required in another place, but the original data will have to stay.

I think you need a macro. There are plenty of people who are much more comfortable with VBA than I am, but it certainly should be possible to create a routine to find a row, cut it and paste it somewhere else.
 
D

Dave Peterson

Couldn't you do:

Find|Cut|paste|delete the original now empty row
or
find|copy|paste|delete the original still filled row

Something like:

Option Explicit
Sub testme()

Dim fWks As Worksheet
Dim tWks As Worksheet
Dim DestCell As Range
Dim FoundCell As Range

Set fWks = Worksheets("sheet2")
Set tWks = Worksheets("sheet1")

With tWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With fWks.UsedRange
Set FoundCell = .Cells.Find(what:="qwer", _
after:=.Cells(.Cells.Count), _
searchdirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.EntireRow.Copy _
Destination:=DestCell
FoundCell.EntireRow.Delete
End If
End With

End Sub
 
Top