Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EditGo ToSpecialBlanks | Excel Discussion (Misc queries) | |||
Edit Paste Special Formulas | Excel Worksheet Functions | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
Edit--Find special characters | Excel Programming | |||
Error handling with a handling routine | Excel Programming |