The macro takes a very long time to run
I am sleeping sorry, the code is not correct.

Will post a good example after dinner and test it first for you
You can do two things the same time <g



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" wrote in message ...
Use this to test

With Sheets("Sheet1").Range("A:B")



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Youlan" wrote in message ...
Hi Ron,

Thanks for your response but I'm having a little problem.

The macro takes a very long time to run and I'm not able to use excel for
that time. I actually have to press escape in order for it to stop and the I
get a runtime error (unable to get the findnext property of the range class).

Also the first time I ran it it moved the 699999 to position B1 as opposed
to the 3 rows above where it was originally. When I tried to run it again
though it moved it to the correct position. I'm not sure why this would have
happened. I tried running it again (just a while ago) and it caused excel to
"hang".

If you're still able to help I'd like to expand my request a little:

The following info will always be in columns A & B:

Parent & 699999 respectively

They will appear nowhere else in the worksheet so once found we would'nt
have to search for them again.

Once found I would like to move both of them to positions three rows
directly above.


"Ron de Bruin" wrote:

Hi Youlan

Try this one for this range (all cells in Sheet1)
With Sheets("Sheet1").UsedRange

Try it on a copy of your workbook

Sub test()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim I As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

MyArr = Array(699999)

'Search Column or range
With Sheets("Sheet1").UsedRange

For I = LBound(MyArr) To UBound(MyArr)

Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
If Rng.Row 3 Then
Rng.Offset(-3, 0).Value = Rng.Value
Rng.Value = ""
End If
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address < FirstAddress
End If
Next I
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Youlan" wrote in message ...
Hi,

I'm using MS Excel 2002 and I'm trying to write a code to FIND "699999" in a
worsheet and once found, move (or copy and paste) it three rows above (but
same column) from where it was originally. I need a code because this search
criteria does not always have the same cell reference but where I need it to
be placed is always three rows above.

I would greatly appreciate any help with this. Thanks in advance.

Regards,