Hi,
I'm a VB newbie, and I'm learning as I go.
I've got a macro that searches one spreadsheet for an account number
then cuts the whole row that the account number appears on, and insert
the row into a different spreadsheet. It works fine, unless the accoun
number is not found. If that happens, it returns the Run-time erro
'91' message. All I want it to do if it can't find the account number
is to jump to the end of the procedure, but I can't work out th
syntax.

Here's the code :

__________________________________________________ ____________

Sub test
Windows("book1.xls").Activate
Range("a1").Select
On Error GoTo ERR_1
Cells.Find(What:="123456", After:=ActiveCell
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows
SearchDirection:=xlNext, _
MatchCase:=False).Activate
Rows(ActiveCell.Row).Select
Application.CutCopyMode = False
Selection.Cut
Windows("book2.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Windows("book1.xls").Activate
Rows(ActiveCell.Row).Delete
ERR_1:
End Sub

__________________________________________________ __________


I'm sure this is just me being stupid, but I've spent ages trying t
figure it out and I just don't know where I've gone wrong.
Any helpful comments will be gratefully received.
Many thanks,
Jo

--
jonraywort
-----------------------------------------------------------------------
jonrayworth's Profile: http://www.excelforum.com/member.php...fo&userid=3596
View this thread: http://www.excelforum.com/showthread.php?threadid=55751

Change your Find code to

Dim FoundCell As Range
Set FoundCell = Cells.Find(What:="123456", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Sub
End If

Note that the .Activate was removed from the call to Find.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"jonrayworth"
wrote
in message
...

Hi,
I'm a VB newbie, and I'm learning as I go.
I've got a macro that searches one spreadsheet for an account
number,
then cuts the whole row that the account number appears on, and
inserts
the row into a different spreadsheet. It works fine, unless the
account
number is not found. If that happens, it returns the Run-time
error
'91' message. All I want it to do if it can't find the account
number,
is to jump to the end of the procedure, but I can't work out
the
syntax.

Here's the code :

__________________________________________________ ____________

Sub test
Windows("book1.xls").Activate
Range("a1").Select
On Error GoTo ERR_1
Cells.Find(What:="123456", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
Rows(ActiveCell.Row).Select
Application.CutCopyMode = False
Selection.Cut
Windows("book2.xls").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Windows("book1.xls").Activate
Rows(ActiveCell.Row).Delete
ERR_1:
End Sub

__________________________________________________ __________


I'm sure this is just me being stupid, but I've spent ages
trying to
figure it out and I just don't know where I've gone wrong.
Any helpful comments will be gratefully received.
Many thanks,
Jon


--
jonrayworth
------------------------------------------------------------------------
jonrayworth's Profile:
http://www.excelforum.com/member.php...o&userid=35960
View this thread:
http://www.excelforum.com/showthread...hreadid=557516