on error

[tina]

Hi
I have a macro with a selection.find when the code that it is trying to find
is not in selection its trapped by on error goto myerr this is in a for each
cell in range and next part of macro .it works first time error happens but
not 2nd?
the appropiate part of code is below any ideas?
Thanks
Tina
col = Range("myCodes").Column
firstRow = Range("MyCodes").Rows(1).Row
lastRow = Range("Mycodes").Rows(Range("MyCodes").Rows.Count).Row
For i = lastRow To firstRow Step -1
MYCell = Cells(i, col)
myvalue = Cells(i, col).Offset(0, 6).Value - 1
If myvalue > 0 Then
Cells(i, col).Offset(1, 0).Resize(myvalue).EntireRow.Insert
Cells(i, col).EntireRow.Copy
Cells(i, 1).Resize(myvalue + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("COMPONENTS.xls").Activate
Columns("A:A").Select
Selection.Find(What:=MYCell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 1).Resize(myvalue + 1, 4).Select
Selection.Copy
Windows("PLANNEDJOBS.xls").Activate
Cells(i, 12).Select
ActiveSheet.Paste
Else
Windows("COMPONENTS.xls").Activate
On Error GoTo MYERR
Columns("A:A").Select
Selection.Find(What:=MYCell, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Range("b1:e1").Copy
Windows("PLANNEDJOBS.xls").Activate
Cells(i, 12).Select
ActiveSheet.Paste
End If
MYERR: Windows("PLANNEDJOBS.xls").Activate
Next

 

[Tom Ogilvy]

Dim rng as Range
col = Range("myCodes").Column
firstRow = Range("MyCodes").Rows(1).Row
lastRow = Range("Mycodes").Rows(Range("MyCodes").Rows.Count).Row
For i = lastRow To firstRow Step -1
MYCell = Cells(i, col)
myvalue = Cells(i, col).Offset(0, 6).Value - 1
If myvalue > 0 Then
Cells(i, col).Offset(1, 0).Resize(myvalue).EntireRow.Insert
Cells(i, col).EntireRow.Copy
Cells(i, 1).Resize(myvalue + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("COMPONENTS.xls").Activate
set rng = Columns("A:A").Find(What:=MYCell, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.Select
ActiveCell.Offset(0, 1).Resize(myvalue + 1, 4).Select
Selection.Copy
Windows("PLANNEDJOBS.xls").Activate
Cells(i, 12).Select
ActiveSheet.Paste
Else
Windows("COMPONENTS.xls").Activate
Set rng = Columns("A:A").Find(What:=MYCell, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
if not rng is nothing then
rng.select
ActiveCell.Range("b1:e1").Copy
Windows("PLANNEDJOBS.xls").Activate
Cells(i, 12).Select
ActiveSheet.Paste
End If
End If
Windows("PLANNEDJOBS.xls").Activate
Next