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? |
"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 |
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 |
All times are GMT +1. The time now is 01:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com