VBA Error Handling

[CROY1985]

Hi all

I have a routine for which i include some error handling.

It basically loops through sheets, and then for each sheet loops through a number of filters and copies data across.

For weekx = weekfrom To Weekto
    ThisWorkbook.Activate
    datasheetname = Sheets("Weeks").Cells(weekx, 1).Value & ".xlsx"
    Datasheet = salesdatasheetpath & datasheetname
    On Error GoTo ErrHandler1
    Workbooks.Open Filename:=Datasheet
Call SortDatasheet


For filterx = 1 To Filterto
    
    
    On Error GoTo ErrHandler2
    MsgBox datasheetname
    Workbooks(datasheetname).Activate
    MsgBox ThisWorkbook.Sheets("Control").Cells(11 + filterx, 1)
    searchfor = ThisWorkbook.Sheets("Control").Cells(11 + filterx, 1)
    Set C = Range("D:D")
    Set where = C.Find(what:=searchfor, after:=C(1))
    startrow = where.Row
    Set where = C.Find(what:=searchfor, after:=C(1), searchdirection:=xlPrevious)
    endrow = where.Row
    Range(startrow & ":" & endrow).Copy


ThisWorkbook.Activate
Sheets("Dataset").Select
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Select
ActiveSheet.Paste
Workbooks(datasheetname).Activate


Continue:
Next filterx
Workbooks(datasheetname).Close
ThisWorkbook.Activate


Next weekx

The first time there is an error it uses the go to errhandler2, however the second time it skips the error handler and i get Runtime Error 91 Object Variable or With Variable not set. I want it to go to errhandler2 each time it encounters an error: not just the first time.

The error occurs at:
on startrow = where.Row (i.e startrow = nothing, at that point in the routine)

Any ideas?

Thanks

Chris

 

[Fluff]

As you haven't included the error handler code, it's difficult to help.
But I'd recommend you read this it has pretty much everything you need to know about handling errors.

 

[RoryA]

There are better ways to handle this anyway, without an error handler, such as testing to see if you found what you were looking for:

    Set where = C.Find(what:=searchfor, after:=C(1))
    If not where is nothing then
       startrow = where.Row
       Set where = C.Find(what:=searchfor, after:=C(1), searchdirection:=xlPrevious)
       endrow = where.Row
       Range(startrow & ":" & endrow).Copy ThisWorkbook.Sheets("Dataset").Cells(ThisWorkbook.Sheets("Dataset").UsedRange.Rows.Count + 1, 1)
End If

 

[Legacy 421546]

Basically: When the error happens you must reset the error handler in preparation for a possible next error. You do this with On Error Goto 0.
Read the link provided by Fluff. It explains this and much more.

 

[Joe4]

Basically: When the error happens you must reset the error handler in preparation for a possible next error. You do this with On Error Goto 0.
Read the link provided by Fluff. It explains this and much more.

Whenever possible, it is usually preferrable to avoid the error altogether, like Rory demonstrated.
In this case, it is pretty easy to avoid the error.

 

[RoryA]

To clarify, On Error Goto 0 is not enough to reset an active exception. You need a Resume statement (or On Error Goto -1 if you like writing horrible code)