Error Handling - EditGo ToSpecial
 

I am having trouble trapping certain kinds of errors in Visual Basic macros.
Specifically I can't seem to trap errors related to the "EditGo ToSpecial"
variety.

For example, if two columns are selected, and I try to locate rowdifferences
between the columns using:

Selection.RowDifferences(Activecell).Select

will work fine if there are row diffrences. If there are no differences, I
get error 1004, which I can't trap using "On Error" handling.

I have the same problem if the Macro tries to look for Blank cells.

Does anyone have a solution for how to trap and handle these errors?

Error Handling - EditGo ToSpecial
 

You are best off to handle this with a range object something like this

dim rng as range

on error resume next
set rng = Selection.RowDifferences(Activecell)
on error goto 0

if not rng is nothing then rng.select

--
HTH...

Jim Thomlinson


"Felix Dalldorf" wrote:

I am having trouble trapping certain kinds of errors in Visual Basic macros.
Specifically I can't seem to trap errors related to the "EditGo ToSpecial"
variety.

For example, if two columns are selected, and I try to locate rowdifferences
between the columns using:

Selection.RowDifferences(Activecell).Select

will work fine if there are row diffrences. If there are no differences, I
get error 1004, which I can't trap using "On Error" handling.

I have the same problem if the Macro tries to look for Blank cells.

Does anyone have a solution for how to trap and handle these errors?

Error Handling - EditGo ToSpecial
 

This is a different way to get to the same problem. When the macro encounters
the line:
set rng = Selection.RowDifferences(Activecell)

the 1004 error condition is raised. The problem is there is no way I have
found around the 1004 error as it is not one you can trap using "On Error"
handling according to the documentation.

What I have found in practice is that the "On Error" condition does work
sometimes. In my current sheet, I have 4 sets of columns I am trying to loop
through to compare. The first set has row differences so the range function
evaluates fine.
The second set has no row differences, but the "On Error Go To" function
works to divert the processing for the null condition.
The third set has no row differences but the macro dies with error 1004.

Reseting and clearing the error conditions makes no difference.


"Jim Thomlinson" wrote:

You are best off to handle this with a range object something like this

dim rng as range

on error resume next
set rng = Selection.RowDifferences(Activecell)
on error goto 0

if not rng is nothing then rng.select

--
HTH...

Jim Thomlinson


"Felix Dalldorf" wrote:

I am having trouble trapping certain kinds of errors in Visual Basic macros.
Specifically I can't seem to trap errors related to the "EditGo ToSpecial"
variety.

For example, if two columns are selected, and I try to locate rowdifferences
between the columns using:

Selection.RowDifferences(Activecell).Select

will work fine if there are row diffrences. If there are no differences, I
get error 1004, which I can't trap using "On Error" handling.

I have the same problem if the Macro tries to look for Blank cells.

Does anyone have a solution for how to trap and handle these errors?

Error Handling - EditGo ToSpecial - EXAMPLE
 

To recreate the error Build a worksheet with these cell entries:

A B C D E F G <- column
1 3 7 2 3 7 2
2 9 0 9 5 0 9
3 5 1 2 5 1 2
^
row

Then step through macro:

Sub Sample()

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

Err1:
On Error GoTo Err2
Range("a1:c1").Select

For Each cell In Selection
Union(cell.EntireColumn, cell.Offset(, 4).EntireColumn).Select
Selection.RowDifferences(ActiveCell).Select
Selection.Interior.ColorIndex = 40
Err2:
Next cell

End Sub

In the linear code in the first part of the macro, the first rowdifference
command will select cell E2 and the color will change.
The second rowdifference will find no differences, but the "On Error"
handling works transferring control to the Err1 label so the 3rd
rowdifference is never run.

In the loop in the second part of the macro, the first time through the
rowdiffernce will select cell E2 and change the color again.
The second time through the loop, it aborts with error 1004. I can find no
way to trap or control this error.

The processes are performing the same type of compare, yet one works and the
other doesn't. Anybody know why?
-----

"Felix Dalldorf" wrote:

This is a different way to get to the same problem. When the macro encounters
the line:
set rng = Selection.RowDifferences(Activecell)

the 1004 error condition is raised. The problem is there is no way I have
found around the 1004 error as it is not one you can trap using "On Error"
handling according to the documentation.

What I have found in practice is that the "On Error" condition does work
sometimes. In my current sheet, I have 4 sets of columns I am trying to loop
through to compare. The first set has row differences so the range function
evaluates fine.
The second set has no row differences, but the "On Error Go To" function
works to divert the processing for the null condition.
The third set has no row differences but the macro dies with error 1004.

Reseting and clearing the error conditions makes no difference.


"Jim Thomlinson" wrote:

You are best off to handle this with a range object something like this

dim rng as range

on error resume next
set rng = Selection.RowDifferences(Activecell)
on error goto 0

if not rng is nothing then rng.select

--
HTH...

Jim Thomlinson


"Felix Dalldorf" wrote:

I am having trouble trapping certain kinds of errors in Visual Basic macros.
Specifically I can't seem to trap errors related to the "EditGo ToSpecial"
variety.

For example, if two columns are selected, and I try to locate rowdifferences
between the columns using:

Selection.RowDifferences(Activecell).Select

will work fine if there are row diffrences. If there are no differences, I
get error 1004, which I can't trap using "On Error" handling.

I have the same problem if the Macro tries to look for Blank cells.

Does anyone have a solution for how to trap and handle these errors?