Error Handler only works once

[aagray]

Hello,

I'm trying to catch an error using "on error goto line 1", whenever a
tab in another file that I'm referencing does not exist.

It seems to work the first time around, but not the second. I've
attached the code.

Any help would be greatly appreciated.

Regard,
Anita

Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress As Range, myPrice
Dim myVendor As String, myProduct
Dim m
j = 1
k = 1
l = 2
Application.ScreenUpdating = False
Windows("trial.xls").Activate
Do Until Cells(k, j) = ""

If Cells(k, j).Value = "f" Then
myVendor = Cells(k, j).Offset(0, 6).Value
myProduct = Cells(k, j).Offset(0, 7).Value
Cells(k, 2).Value = myVendor
Cells(k, 3).Value = myProduct
Windows("Code.xls").Activate

On Error GoTo line1
Workbooks("Code.xls").Sheets(myVendor).Select
Columns("F:F").Select
Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
after:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
cell.Activate
Set cell = ActiveCell

' cell.Activate
myPrice = ActiveCell.Offset(0, 1).Value

If Not cell Is Nothing Then
Windows("trial.xls").Activate
Cells(k, 12).Value = myPrice
End If
' Windows("trial.xls").Activate
' Cells(k, 12).Value = myPrice

Windows("trial.xls").Activate



Else
Cells(k, 2).Value = Cells(k, j).Offset(0, 9).Value
End If
line1:

Windows("trial.xls").Activate
k = k + 1

Loop

Application.ScreenUpdating = True
End Sub


A

*** Sent via Developersdex http://www.developersdex.com ***

[Jim Thomlinson]

Once an error has been generated you leave the normal thread of execution and
start processing under the error handler. To resume normal exectuion requires
the resume key word. Since you never resume normal exectuion after your first
error you are now processing under the error handler. Once in the error
handler you can no longer handle any subsequent errors. IMO this is an
inappropriate use of the error handler (catching an error that should never
be generated in the first place). You should verify that a sheet exists prior
to using that sheet. Something like this...

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

Sub atryThisSix()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myAddress, theAddress As Range, myPrice
Dim myVendor As String, myProduct
Dim m
j = 1
k = 1
l = 2
Application.ScreenUpdating = False
Windows("trial.xls").Activate
Do Until Cells(k, j) = ""

If Cells(k, j).Value = "f" Then
myVendor = Cells(k, j).Offset(0, 6).Value
myProduct = Cells(k, j).Offset(0, 7).Value
Cells(k, 2).Value = myVendor
Cells(k, 3).Value = myProduct
Windows("Code.xls").Activate

if sheetexists(Workbooks("Code.xls").Sheets(myVendor)) then 'New if
Workbooks("Code.xls").Sheets(myVendor).Select
Columns("F:F").Select
Dim cell As Range

Set cell = Columns("f:f").Find(What:=myProduct, _
after:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
cell.Activate
Set cell = ActiveCell

' cell.Activate
myPrice = ActiveCell.Offset(0, 1).Value

If Not cell Is Nothing Then
Windows("trial.xls").Activate
Cells(k, 12).Value = myPrice
End If
' Windows("trial.xls").Activate
' Cells(k, 12).Value = myPrice

Windows("trial.xls").Activate



Else
Cells(k, 2).Value = Cells(k, j).Offset(0, 9).Value
End If
end if 'new end if
Windows("trial.xls").Activate
k = k + 1

Loop

Application.ScreenUpdating = True
End Sub

--
HTH...

Jim Thomlinson


"aagray" wrote:

> Hello,
>
> I'm trying to catch an error using "on error goto line 1", whenever a
> tab in another file that I'm referencing does not exist.
>
> It seems to work the first time around, but not the second. I've
> attached the code.
>
> Any help would be greatly appreciated.
>
> Regard,
> Anita
>
> Sub atryThisSix()
> Dim i As Integer
> Dim j As Integer
> Dim k As Integer
> Dim myAddress, theAddress As Range, myPrice
> Dim myVendor As String, myProduct
> Dim m
> j = 1
> k = 1
> l = 2
> Application.ScreenUpdating = False
> Windows("trial.xls").Activate
> Do Until Cells(k, j) = ""
>
> If Cells(k, j).Value = "f" Then
> myVendor = Cells(k, j).Offset(0, 6).Value
> myProduct = Cells(k, j).Offset(0, 7).Value
> Cells(k, 2).Value = myVendor
> Cells(k, 3).Value = myProduct
> Windows("Code.xls").Activate
>
> On Error GoTo line1
> Workbooks("Code.xls").Sheets(myVendor).Select
> Columns("F:F").Select
> Dim cell As Range
>
> Set cell = Columns("f:f").Find(What:=myProduct, _
> after:=ActiveCell, _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> cell.Activate
> Set cell = ActiveCell
>
> ' cell.Activate
> myPrice = ActiveCell.Offset(0, 1).Value
>
> If Not cell Is Nothing Then
> Windows("trial.xls").Activate
> Cells(k, 12).Value = myPrice
> End If
> ' Windows("trial.xls").Activate
> ' Cells(k, 12).Value = myPrice
>
> Windows("trial.xls").Activate
>
>
>
> Else
> Cells(k, 2).Value = Cells(k, j).Offset(0, 9).Value
> End If
> line1:
>
> Windows("trial.xls").Activate
> k = k + 1
>
> Loop
>
> Application.ScreenUpdating = True
> End Sub
>
>
> A
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

[Jim Thomlinson]

Sorry, should be

if SheetExists(myVendor) then 'New if

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

> Once an error has been generated you leave the normal thread of execution and
> start processing under the error handler. To resume normal exectuion requires
> the resume key word. Since you never resume normal exectuion after your first
> error you are now processing under the error handler. Once in the error
> handler you can no longer handle any subsequent errors. IMO this is an
> inappropriate use of the error handler (catching an error that should never
> be generated in the first place). You should verify that a sheet exists prior
> to using that sheet. Something like this...
>
> Public Function SheetExists(SName As String, _
> Optional ByVal Wb As Workbook) As Boolean
> 'Chip Pearson
> On Error Resume Next
> If Wb Is Nothing Then Set Wb = ThisWorkbook
> SheetExists = CBool(Len(Wb.Sheets(SName).Name))
> End Function
>
> Sub atryThisSix()
> Dim i As Integer
> Dim j As Integer
> Dim k As Integer
> Dim myAddress, theAddress As Range, myPrice
> Dim myVendor As String, myProduct
> Dim m
> j = 1
> k = 1
> l = 2
> Application.ScreenUpdating = False
> Windows("trial.xls").Activate
> Do Until Cells(k, j) = ""
>
> If Cells(k, j).Value = "f" Then
> myVendor = Cells(k, j).Offset(0, 6).Value
> myProduct = Cells(k, j).Offset(0, 7).Value
> Cells(k, 2).Value = myVendor
> Cells(k, 3).Value = myProduct
> Windows("Code.xls").Activate
>
> if sheetexists(Workbooks("Code.xls").Sheets(myVendor)) then 'New if
> Workbooks("Code.xls").Sheets(myVendor).Select
> Columns("F:F").Select
> Dim cell As Range
>
> Set cell = Columns("f:f").Find(What:=myProduct, _
> after:=ActiveCell, _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> cell.Activate
> Set cell = ActiveCell
>
> ' cell.Activate
> myPrice = ActiveCell.Offset(0, 1).Value
>
> If Not cell Is Nothing Then
> Windows("trial.xls").Activate
> Cells(k, 12).Value = myPrice
> End If
> ' Windows("trial.xls").Activate
> ' Cells(k, 12).Value = myPrice
>
> Windows("trial.xls").Activate
>
>
>
> Else
> Cells(k, 2).Value = Cells(k, j).Offset(0, 9).Value
> End If
> end if 'new end if
> Windows("trial.xls").Activate
> k = k + 1
>
> Loop
>
> Application.ScreenUpdating = True
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "aagray" wrote:
>
> > Hello,
> >
> > I'm trying to catch an error using "on error goto line 1", whenever a
> > tab in another file that I'm referencing does not exist.
> >
> > It seems to work the first time around, but not the second. I've
> > attached the code.
> >
> > Any help would be greatly appreciated.
> >
> > Regard,
> > Anita
> >
> > Sub atryThisSix()
> > Dim i As Integer
> > Dim j As Integer
> > Dim k As Integer
> > Dim myAddress, theAddress As Range, myPrice
> > Dim myVendor As String, myProduct
> > Dim m
> > j = 1
> > k = 1
> > l = 2
> > Application.ScreenUpdating = False
> > Windows("trial.xls").Activate
> > Do Until Cells(k, j) = ""
> >
> > If Cells(k, j).Value = "f" Then
> > myVendor = Cells(k, j).Offset(0, 6).Value
> > myProduct = Cells(k, j).Offset(0, 7).Value
> > Cells(k, 2).Value = myVendor
> > Cells(k, 3).Value = myProduct
> > Windows("Code.xls").Activate
> >
> > On Error GoTo line1
> > Workbooks("Code.xls").Sheets(myVendor).Select
> > Columns("F:F").Select
> > Dim cell As Range
> >
> > Set cell = Columns("f:f").Find(What:=myProduct, _
> > after:=ActiveCell, _
> > LookIn:=xlFormulas, _
> > LookAt:=xlPart, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False)
> > cell.Activate
> > Set cell = ActiveCell
> >
> > ' cell.Activate
> > myPrice = ActiveCell.Offset(0, 1).Value
> >
> > If Not cell Is Nothing Then
> > Windows("trial.xls").Activate
> > Cells(k, 12).Value = myPrice
> > End If
> > ' Windows("trial.xls").Activate
> > ' Cells(k, 12).Value = myPrice
> >
> > Windows("trial.xls").Activate
> >
> >
> >
> > Else
> > Cells(k, 2).Value = Cells(k, j).Offset(0, 9).Value
> > End If
> > line1:
> >
> > Windows("trial.xls").Activate
> > k = k + 1
> >
> > Loop
> >
> > Application.ScreenUpdating = True
> > End Sub
> >
> >
> > A
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> >