On Error Resume Next
 

What causes On Error Resume Next to be ignored ??

I have a piece of code that exploits the Collection limit of unique key
values only, however it still throws an error when trying to add a duplicate
key to the Collection - as if the On Error Resume Next is being ignored

Dim NoDupes As New Collection
Dim AllCells As Range, Cell As Range
Set AllCells = Range("A1:A100")

' The next statement ignores the error caused by attempting to add a
duplicate key to the collection.
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
' Resume normal error handling
On Error GoTo 0

--
Cheers
Nigel

On Error Resume Next
 

Hi Nigel,

You need to restore default error handling *within* the For Each ... Next
loop, i.e.:

On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Resume normal error handling
On Error GoTo 0
Next Cell

With your code error hanling is only restored one the collection loading is
complete, hence the error.

---
Regards,
Norman



"Nigel" wrote in message
...
What causes On Error Resume Next to be ignored ??

I have a piece of code that exploits the Collection limit of unique key
values only, however it still throws an error when trying to add a
duplicate
key to the Collection - as if the On Error Resume Next is being ignored

Dim NoDupes As New Collection
Dim AllCells As Range, Cell As Range
Set AllCells = Range("A1:A100")

' The next statement ignores the error caused by attempting to add a
duplicate key to the collection.
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
' Resume normal error handling
On Error GoTo 0

--
Cheers
Nigel

On Error Resume Next
 

Your code worked for me.

In the VBE, try:
Tools|Options|General tab
Change the error trapping setting to "break on unhandled errors"

If I had "break on all errors" set, then I got an error.

Nigel wrote:

What causes On Error Resume Next to be ignored ??

I have a piece of code that exploits the Collection limit of unique key
values only, however it still throws an error when trying to add a duplicate
key to the Collection - as if the On Error Resume Next is being ignored

Dim NoDupes As New Collection
Dim AllCells As Range, Cell As Range
Set AllCells = Range("A1:A100")

' The next statement ignores the error caused by attempting to add a
duplicate key to the collection.
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
' Resume normal error handling
On Error GoTo 0

--
Cheers
Nigel

--

Dave Peterson

On Error Resume Next
 

Hi Dave,

Thank you, you are, of course correct.

What moving the On Error Goto 0 into the loop does do is to clear the error
at each pass and, thus, enable code to be run in response to any error.

---
Regards,
Norman



"Dave Peterson" wrote in message
...
Your code worked for me.

In the VBE, try:
Tools|Options|General tab
Change the error trapping setting to "break on unhandled errors"

If I had "break on all errors" set, then I got an error.

Nigel wrote:

What causes On Error Resume Next to be ignored ??

I have a piece of code that exploits the Collection limit of unique key
values only, however it still throws an error when trying to add a
duplicate
key to the Collection - as if the On Error Resume Next is being ignored

Dim NoDupes As New Collection
Dim AllCells As Range, Cell As Range
Set AllCells = Range("A1:A100")

' The next statement ignores the error caused by attempting to add a
duplicate key to the collection.
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
' Resume normal error handling
On Error GoTo 0

--
Cheers
Nigel

--

Dave Peterson