Multiple On Error Goto Statements in VBA

[bellsea]

Hi,

I am trying to write a macro to open up 4 different workbooks (1 at a time), filter the data based on a given criteria, and then select a range of visible cells only, and then copy and past them into a master workbook.

The issue is with the selection of visible cells. If there is no data in the worksheet then the selection will consist of visible cells only and it generates an error:

Run-time error '1004':

no cells were found.

I used a On Error Goto 10 statement and made it past the first occurence of this error, tried using a On Error Goto 11 on the next file but it still stops.

At the risk of looking like a putz with my first attempt at VB, I have attached the code that I wrote for this macro:

Sub FILEIMPORT()
'
' FILEIMPORT Macro
' Macro recorded 31/01/2008 by Sean Bell
'

Dim pricingdir As String
Dim categorydir As String
Dim skudir As String
Dim reportingdir As String
Dim pricingfile As String
Dim categoryfile As String
Dim skufile As String
Dim reportingfile As String
Dim demandgroup As String
Dim mastername As String

Sheets("Update Tab").Activate
pricingdir = Worksheets("Update Tab").Range("PRICING_DIR")
categorydir = Worksheets("Update Tab").Range("CATEGORY_DIR")
skudir = Worksheets("Update Tab").Range("SKU_DIR")
reportingdir = Worksheets("Update Tab").Range("REPORTING_DIR")
demandgroup = Worksheets("Update Tab").Range("DEMAND_GROUP")
pricingfile = Worksheets("Update Tab").Range("PRICING_FILE")
categoryfile = Worksheets("Update Tab").Range("CATEGORY_FILE")
skufile = Worksheets("Update Tab").Range("SKU_FILE")
reportingfile = Worksheets("Update Tab").Range("REPORTING_FILE")
demandgroup = Worksheets("Update Tab").Range("DEMAND_GROUP")
mastername = Worksheets("Update Tab").Range("MASTER_NAME")


Sheets("Food_Category").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BE1921").Select
Selection.ClearContents
Sheets("Food_SKU").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BG129").Select
Selection.ClearContents
Sheets("Food_Reporting").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BD1921").Select
Selection.ClearContents
Sheets("Food_Pricing").Select
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Update Tab").Select
Windows(categoryfile).Activate
Range("C1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range("A4:BE1892").Select
On Error GoTo 10
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
Windows(mastername).Activate
Sheets("FOOD_CATEGORY").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
10 Windows(pricingfile).Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:E44").Select
Application.CutCopyMode = False
Selection.Copy
Windows(mastername).Activate
Sheets("Food_Pricing").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(reportingfile).Activate
Range("C1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range("A4:BD1892").Select
On Error GoTo 11
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows(mastername).Activate
Sheets("Food_Reporting").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
11 Windows(skufile).Activate

Range("E1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A4:BG100").Select
On Error GoTo 12
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows(mastername).Activate
Sheets("Food_SKU").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
12 Windows(mastername).Activate
Sheets("Update Tab").Select
End Sub

Thanks in advance for your assistance.

 

[Fazza]

Hi Sean,

When posting, please enclose code within tags. After the code [/code] and before

Can you describe in words the whole task? There is an approach that can get the data without needing to open the files and I think it would be very suitable.

regards, Fazza

 

[Fazza]

regarding the error, common treatment is

'selecting special cells gives an error when there are no cells found

'if there is an error, ignore it and continue
On Error Resume Next
Range("whatever").SpecialCells(xlCellTypeVisible).something
'reset normal error handling
On Error GoTo 0



'OR
'if there is an error, ignore it and continue
On Error Resume Next
Range("whatever").SpecialCells(xlCellTypeVisible).something

If Err = 0 Then
  'there was no error, do whatever with the special cells
Else
  'there was an error. that is, no special cells were found
End If

'reset normal error handling
On Error GoTo 0

 

[bellsea]

Hi Fazza,

the first half of the macro is selecting sheets in the master workbook, deleting all the data on 4 tabs (Food_Category, Food_Sku, Food_Reporting, Food_Pricing), it is then selecting another workbook (categoryfile) that was opened by a previous macro. I am then filtering the data based on a named range found in the master workbook (demandgroup), selecting the remaining data range, selecting visible cells, copying, selecting the master workbook, pasting in the data and moving on to the next file and performing a very similar series of steps and repeating the process 2 more times.

It's the selection of the visible cells in 3 of the 4 files that is creating an issue.

If I use a On error goto next, do I only need to use this statement once, or do you need to place it in front of each potential error? Sorry - I'm way over my head on this stuff.

thanks for your response.

Sean

 

[bellsea]

Thanks Fazza,

I used the On Error Resume Next and it worked like a charm!

Appreciate the help.

Cheers.

 

[Fazza]

Within VBA the built in help is good. Info below is copied from there.

I'm sure if you google there will also be lots of good information.

The "on error resume next" turns off VBA's normal error handling. Poor programming practice could be to do this at the beginning of your code and it will run until the end - skipping over errors and doing the best it can. This is not recommended. Best is to set up your code to deal with errors in a controlled way. When using the "on error resume next" good practice would be to apply it, per my earlier example, on as restricted a section of code as possible. Otherwise you risk have errors occuring, that maybe you didn't expect, and this leads to the whole program giving wrong results. After you've used the line, for example before a special cells, use "on error goto 0" to reset the normal error handling. This is about as simple as it gets; in a more complex/comprehensive code there are other ways to handle things. There are whole books on the subject...

On Error Statement


Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine.

Syntax

On Error GoTo line

On Error Resume Next

On Error GoTo 0

The On Error statement syntax can have any of the following forms:

Statement Description
On Error GoTo line Enables the error-handling routine that starts at line specified in the required line argument. The line argument is any line label or line number. If a run-time error occurs, control branches to line, making the error handler active. The specified line must be in the same procedure as the On Error statement; otherwise, a compile-time error occurs.
On Error Resume Next Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues. Use this form rather than On Error GoTo when accessing objects.
On Error GoTo 0 Disables any enabled error handler in the current procedure.



Remarks

If you don't use an On Error statement, any run-time error that occurs is fatal; that is, an error message is displayed and execution stops.

An "enabled" error handler is one that is turned on by an On Error statement; an "active" error handler is an enabled handler that is in the process of handling an error. If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. Control returns to the calling procedure. If the calling procedure has an enabled error handler, it is activated to handle the error. If the calling procedure's error handler is also active, control passes back through previous calling procedures until an enabled, but inactive, error handler is found. If no inactive, enabled error handler is found, the error is fatal at the point at which it actually occurred. Each time the error handler passes control back to a calling procedure, that procedure becomes the current procedure. Once an error is handled by an error handler in any procedure, execution resumes in the current procedure at the point designated by the Resume statement.

Note An error-handling routine is not a Sub procedure or Function procedure. It is a section of code marked by a line label or line number.

Error-handling routines rely on the value in the Number property of the Err object to determine the cause of the error. The error-handling routine should test or save relevant property values in the Err object before any other error can occur or before a procedure that might cause an error is called. The property values in the Err object reflect only the most recent error. The error message associated with Err.Number is contained in Err.Description.

On Error Resume Next causes execution to continue with the statement immediately following the statement that caused the run-time error, or with the statement immediately following the most recent call out of the procedure containing the On Error Resume Next statement. This statement allows execution to continue despite a run-time error. You can place the error-handling routine where the error would occur, rather than transferring control to another location within the procedure. An On Error Resume Next statement becomes inactive when another procedure is called, so you should execute an On Error Resume Next statement in each called routine if you want inline error handling within that routine.

Note The On Error Resume Next construct may be preferable to On Error GoTo when handling errors generated during access to other objects. Checking Err after each interaction with an object removes ambiguity about which object was accessed by the code. You can be sure which object placed the error code in Err.Number, as well as which object originally generated the error (the object specified in Err.Source).

On Error GoTo 0 disables error handling in the current procedure. It doesn't specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. Without an On Error GoTo 0 statement, an error handler is automatically disabled when a procedure is exited.

To prevent error-handling code from running when no error has occurred, place an Exit Sub, Exit Function, or Exit Property statement immediately before the error-handling routine, as in the following fragment:

Sub InitializeMatrix(Var1, Var2, Var3, Var4)
On Error GoTo ErrorHandler
. . .
Exit Sub
ErrorHandler:
. . .
Resume Next
End Sub

Here, the error-handling code follows the Exit Sub statement and precedes the End Sub statement to separate it from the procedure flow. Error-handling code can be placed anywhere in a procedure.

Untrapped errors in objects are returned to the controlling application when the object is running as an executable file. Within the development environment, untrapped errors are only returned to the controlling application if the proper options are set. See your host application's documentation for a description of which options should be set during debugging, how to set them, and whether the host can create classes.

If you create an object that accesses other objects, you should try to handle errors passed back from them unhandled. If you cannot handle such errors, map the error code in Err.Number to one of your own errors, and then pass them back to the caller of your object. You should specify your error by adding your error code to the vbObjectError constant. For example, if your error code is 1052, assign it as follows:

Err.Number = vbObjectError + 1052

Note System errors during calls to Windows dynamic-link libraries (DLL) or Macintosh code resources do not raise exceptions and cannot be trapped with Visual Basic error trapping. When calling DLL functions, you should check each return value for success or failure (according to the API specifications), and in the event of a failure, check the value in the Err object's LastDLLError property. LastDLLError always returns zero on the Macintosh.

 

[Fazza]

FYI, if I understand the task, using query tables can do what you want without any VBA, without any formulas, without opening the other files. If you're interested, please look for info on 'external data'. regards, Fazza