| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's wrong with this code?
If Selection.SpecialCells(xlConstants) Is Nothing Then
Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
Try this:
If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearC ontents End If "access user" wrote: If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
Try:
On Error Resume Next If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If On Error GoTo 0 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
Or rather:
On Error Resume Next If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearC ontents End If On Error GoTo 0 (Got mixed up with JLGWhiz JLGWhiz's code) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
Hi Guys
Thanks to both of you for replying. Sandy you get the cigar. JLGWhiz - that still gave the same error. Only difference appears to be the 'On Error Resume Next' method. Don't understand why but thanks. 'G'day cobbers' - is that what they say over there? :-) "Sandy Mann" wrote: Try: On Error Resume Next If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If On Error GoTo 0 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
If you're going to use "On error", why not just:
On Error Resume Next Selection.SpecialCells(xlCellTypeConstants).ClearC ontents On Error GoTo 0 === But depending on what the selection is, I'd use: On Error Resume Next intersect(selection,Selection.SpecialCells(xlCellT ypeConstants)).ClearContents On Error GoTo 0 it'll avoid any problems if a single cell is selected. Sandy Mann wrote: Or rather: On Error Resume Next If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearC ontents End If On Error GoTo 0 (Got mixed up with JLGWhiz JLGWhiz's code) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
Thanks Dave - I'll look into that.
James "Dave Peterson" wrote: If you're going to use "On error", why not just: On Error Resume Next Selection.SpecialCells(xlCellTypeConstants).ClearC ontents On Error GoTo 0 === But depending on what the selection is, I'd use: On Error Resume Next intersect(selection,Selection.SpecialCells(xlCellT ypeConstants)).ClearContents On Error GoTo 0 it'll avoid any problems if a single cell is selected. Sandy Mann wrote: Or rather: On Error Resume Next If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearC ontents End If On Error GoTo 0 (Got mixed up with JLGWhiz JLGWhiz's code) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
These virtual cigars are quite good for you :-)
"Sandy Mann" wrote: Just my luck! I give up smoking then I get offered a cigar! <g Thanks for the feedback. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... Hi Guys Thanks to both of you for replying. Sandy you get the cigar. JLGWhiz - that still gave the same error. Only difference appears to be the 'On Error Resume Next' method. Don't understand why but thanks. 'G'day cobbers' - is that what they say over there? :-) "Sandy Mann" wrote: Try: On Error Resume Next If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If On Error GoTo 0 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What's wrong with this code?
This might be what you really wanted.
If Selection.SpecialCells(xlCellTypeConstants).Count < 1 Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearC ontents End If "access user" wrote: Thanks Dave - I'll look into that. James "Dave Peterson" wrote: If you're going to use "On error", why not just: On Error Resume Next Selection.SpecialCells(xlCellTypeConstants).ClearC ontents On Error GoTo 0 === But depending on what the selection is, I'd use: On Error Resume Next intersect(selection,Selection.SpecialCells(xlCellT ypeConstants)).ClearContents On Error GoTo 0 it'll avoid any problems if a single cell is selected. Sandy Mann wrote: Or rather: On Error Resume Next If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearC ontents End If On Error GoTo 0 (Got mixed up with JLGWhiz JLGWhiz's code) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "access user" wrote in message ... If Selection.SpecialCells(xlConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlConstants).ClearContents End If Basically, if the cells in question (either a selection or a range) are already cleared then I do not want the sub run, otherwise I want it run. The above is not trapping that condition as the last line before End If always runs and brings up the error 'no cells' - I do not want this error to be shown, just want the code to terminate in that case. tia James -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What the F@#% is wrong with this code?? | Excel Programming | |||
| HELP! What's wrong with my code? | Excel Programming | |||
| HELP! What's wrong with my code? | Excel Programming | |||
| What's wrong with the code,pls hv a look | Excel Programming | |||
| What's wrong with this bit of code | Excel Programming | |||
Linear Mode
