"On Error GoTo" syntax problem

[jonrayworth]

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

 

[Chip Pearson]

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"
in message
news:[email protected]...