 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008What'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 Try this: If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearContents End If > If Selection.SpecialCells(xlConstants) Is Nothing Then > Exit Sub [quoted text clipped - 10 lines] > tia > James 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  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > If Selection.SpecialCells(xlConstants) Is Nothing Then > Exit Sub [quoted text clipped - 11 lines] > tia > James 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? :-) > Try: > [quoted text clipped - 21 lines] > > tia > > James Just my luck! I give up smoking then I get offered a cigar! <g> Thanks for the feedback. SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi Guys > [quoted text clipped - 34 lines] >> > tia >> > James These virtual cigars are quite good for you :-) > Just my luck! I give up smoking then I get offered a cigar! <g> Thanks > for the feedback. [quoted text clipped - 37 lines] > >> > tia > >> > James Or rather: On Error Resume Next If Selection.SpecialCells(xlCellTypeConstants) Is Nothing Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearContents End If On Error GoTo 0 (Got mixed up with JLGWhiz JLGWhiz's code) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > If Selection.SpecialCells(xlConstants) Is Nothing Then > Exit Sub [quoted text clipped - 11 lines] > tia > James If you're going to use "On error", why not just: On Error Resume Next Selection.SpecialCells(xlCellTypeConstants).ClearContents On Error GoTo 0 === But depending on what the selection is, I'd use: On Error Resume Next intersect(selection,Selection.SpecialCells(xlCellTypeConstants)).ClearContents On Error GoTo 0 it'll avoid any problems if a single cell is selected. > Or rather: > [quoted text clipped - 33 lines] > > tia > > James SignatureDave Peterson Thanks Dave - I'll look into that. James > If you're going to use "On error", why not just: > [quoted text clipped - 48 lines] > > > tia > > > James This might be what you really wanted. If Selection.SpecialCells(xlCellTypeConstants).Count < 1 Then Exit Sub Else Selection.SpecialCells(xlCellTypeConstants).ClearContents End If > Thanks Dave - I'll look into that. > James [quoted text clipped - 51 lines] > > > > tia > > > > James |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.