Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA ON ERROR does not work with SPECIALCELLS
I have a problem trapping errors using
"On Error goto ..." or "On Error Resume Next" "On Error ..." works everywhere except when the offending command uses SpecialCells as in Selection.SpecialCells(xlCellTypeBlanks).Select when not found, the macro errors with Run-time error 1004. The same macros worked fine in xl2000 until I had to install the hotfix from MS Support article 815406. Then the problem started. Just upgraded to xl2003 but the problem remains. All the MS support articles refer only to protected sheets which does not apply. Any ideas on a workaround? |
#2
|
|||
|
|||
"Felix" wrote in message ... I have a problem trapping errors using "On Error goto ..." or "On Error Resume Next" "On Error ..." works everywhere except when the offending command uses SpecialCells as in Selection.SpecialCells(xlCellTypeBlanks).Select when not found, the macro errors with Run-time error 1004. The same macros worked fine in xl2000 until I had to install the hotfix from MS Support article 815406. Then the problem started. Just upgraded to xl2003 but the problem remains. All the MS support articles refer only to protected sheets which does not apply. Any ideas on a workaround? I think you have to loop over the selection and make sure there's at least one blank cell. /Fredrik |
#3
|
|||
|
|||
Felix wrote...
I have a problem trapping errors using "On Error goto ..." or "On Error Resume Next" "On Error ..." works everywhere except when the offending command uses SpecialCells as in Selection.SpecialCells(xlCellTypeBlanks).Select when not found, the macro errors with Run-time error 1004. .... This *should* give an untrappable runtime error. When there are no blank cells, .SpecialCells doesn't return a range object, so the following .Select method call is invalid. You need to use two steps to trap potential failure by .SpecialCells. Dim r As Range On Error Resume Next Set r = Selection.SpecialCells(xlCellTypeBlanks) Err.Clear On Error GoTo 0 If r Is Nothing Then MsgBox "No blank cells" Else r.Select End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions | |||
How to get saved old saved work that was saved over? | Excel Discussion (Misc queries) | |||
Change Path names in copied work book | Excel Worksheet Functions | |||
how is possible working on work sheet by two person in the netwo. | Excel Worksheet Functions | |||
How do I ensure dates inputted are during the work week? | Excel Worksheet Functions |