"On Error" does not always work - Code Sample

[Felix Dalldorf]

There are error types that can not always be trapped using the "On Error"
conditions in Visual Basic.

Here is an example of an error using macro to highlight Row Differences in a
spreadsheet. To recreate the problem build a worksheet with these cells:

A B C D E F G

1 3 7 2 3 7 2

2 9 0 9 5 0 9

3 5 1 2 5 1 2



Then copy paste this macro into a module:

Sub Sample()

On Error GoTo Err1
Range("a:a, e:e").Select
Selection.RowDifferences(ActiveCell).Select
Selection.Interior.ColorIndex = 6
Err1:
On Error GoTo Err2
Range("b:b, f:f").Select
Selection.RowDifferences(ActiveCell).Select
Selection.Interior.ColorIndex = 6
Err2:
On Error GoTo Err3
Range("c:c, g:g").Select
Selection.RowDifferences(ActiveCell).Select
Selection.Interior.ColorIndex = 6
Err3:

End Sub


Stepping through the macro:

The first rowdifference finds a difference, selects cell E2, and changes the
color to yellow.

The second rowdifference finds no cells, and the On Error condition
transfers control to the line after Err2:

The third rowdifference finds no cells, but the On Error condition does not
work, instead the macro errors with "Run-time error '1004': No cells were
found."

There does not appear to be a way to consistently handle the error condition.
Anybody know what I need to do to be able to consistently trp this kind of
error?

 

[Chip Pearson]

You can't simply use On Error Goto to advance to a further
location in your code. When an error occcurs, VBA goes into
"error mode" which means no further error can be trapped until a
RESUME statement is executed.


in message
news:[email protected]...