On Error GoTo Next not skipping code when error.

[RockandGrohl]

Hi all,

Have a sheet I'm filtering and copying the visible cells in this Macro, but in some rare circumstances, once the filter is applied, there are no visible cells.

On Error GoTo SkipTourCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("A12")
NameCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("B12")
DateCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("C12")
CostCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("D12")
ad.Close False

[extra code for when there are cells]

Skip:
ads.Activate
ActiveCell.Offset(1, 0).Activate

The intention is, if there are visible cells to copy them and then start playing with them, but if there aren't, go to Skip, drop down a line and re-run through the loop.

However, on this particular piece of code being looped, the filters result in no visible cells and I get "Run-time error '1004': No cells were found."

So that's an error, but it's not skipping? Any ideas why? Thanks.

 

[Fluff]

Have a look here to see why http://excelmatters.com/2015/03/17/on-error-wtf/

 

[steve the fish]

Include the filter header in the ranges and test for visible cells being greater than 1 before continuing as the header is always visible.

 

[RockandGrohl]

Have a look here to see why http://excelmatters.com/2015/03/17/on-error-wtf/

I'm afraid I don't understand - it doesn't mention what to do if an On Error Goto X fails, just why you shouldn't use Resume Next as a blanket covering error handler and other do's and don'ts


EDIT: Ah, is it because this is probably more than 1st time this error has come into play, and I've effectively not closed off the error?


So I need to write a Resume portion of the code, towards the end where I write where Skip is, but I don't know how to do that

 

[RockandGrohl]

OK, got it.

An error needs an ending, like a formula needs a closing bracket. I did try things like On Error Goto0 or resume next or whatever, but the finished code (condensed) looks like this:

On Error GoTo NoTours
TourCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("A12")
NameCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("B12")
DateCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("C12")
CostCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("D12")
ad.Close False

NoTours:Resume Skip
Skip:
ads.Activate
ActiveCell.Offset(1, 0).Activate


Loop ' Main Loop

So what was happening was in the old way, "On Error GoTo Skip" was activated and that handled the initial error, well there's 4 problem papers in a row at the start of the loop, so when the loop returned to the Range copy segment, it was still skipping that initial error, then another was piled on an excel freaked out.

Lesson learned was that you can only leave an error handle open when directly after the handled error it closes the sub, but if you're in a loop, you need to close that error as I did above by giving a resuming point.

 

[Joe4]

Note that "Err.Clear" may come in handy in situations like this. That allows you to clear/rest the error, in case you have loops/other checks to do.
See: https://www.oreilly.com/library/view/vb-vba/1565923588/1565923588_ch07-677-fm2xml.html