On Error - Only executing once.

  • Thread starter Thread starter NateBuckley
  • Start date Start date
N

NateBuckley

Hello I have a On Error Goto statement, yet it only works once, then It just
ignores and tries to execute although this causes it to crash.

I'm attempting to grab stuff from a collection


Set tmp = lookUpCollection.Item("Monkey"), there is no object with the key
monkey, and it bypasses it on the first go by following my error handling line

On Error Goto ShouldSkip

The ShouldSkip label contains the following

ShouldSkip:
skip = True
GoTo CheckSkip

Why is it only doing it once, I've used this method before and it worked
fine, just all of a suddent I'm getting the error

Invalid Procedure Call or argument.

Thanks in advance.
 
Hi
Not enough info.
"Only works once" - are you in a loop? Let's see it.
What are the scope of the variables? Is skip a public or private
variable being tested in a loop??
Is tmp really an object in a collection of objects, or a variant???

regards
Paul
 
Hi Nate

It's not easy to see what's wrong without the entire code!

Do you set
skip = False
before second loop?

Regards,

Per
 
My Apologies for not posting the code,
please excuse the mess that is the code :)

Option Explicit
Private FoundProducts As Collection
Public Sub lookUp()
Dim skip As Boolean
Dim SingleProduct As C_ProductCode
Dim foundProduct As C_EntireBay
Dim lookUpCollection As Collection
Dim str As String
Dim binCode() As String
Dim prodCode() As String
Dim binNum As Long
Dim prodNum As Long
prodNum = Sheets("LI").Cells(Rows.Count, "K").End(xlUp).Row
binNum = Sheets("LI").Cells(Rows.Count, "A").End(xlUp).Row

Set lookUpCollection = New Collection
Set FoundProducts = New Collection

Dim i As Long
Dim j As Long
For i = 2 To prodNum
prodCode = Split(Sheets("LI").Cells(i, 11).Value, "/")
str = ""
For j = 0 To UBound(prodCode)
str = str & prodCode(j)
Next j
Set SingleProduct = New C_ProductCode
SingleProduct.AtRow = i
SingleProduct.ProductCode = str
On Error Resume Next
lookUpCollection.Add SingleProduct, SingleProduct.ProductCode
On Error GoTo 0
Next i
For i = 2 To binNum
skip = False
str = ""
binCode = Split(Sheets("LI").Cells(i, 6).Value, "/")
For j = 0 To UBound(binCode)
str = str & binCode(j)
Next j
On Error GoTo ShouldSkip
Set SingleProduct = lookUpCollection.Item(str) 'PROBLEMS
CheckSkip:
If skip = False Then
Set foundProduct = New C_EntireBay
foundProduct.Aisle = Sheets("LI").Cells(i, 1).Value
...
...
foundProduct.Allocation = Sheets("LI").Cells(i, 9).Value
foundProduct.Code = SingleProduct.ProductCode
'Sheets("LI").Cells(SingleProduct.AtRow, 12).value = "Exists
within -" & Sheets("LI").Cells(i, 1) & Sheets("LI").Cells(i, 2) &
Sheets("LI").Cells(i, 3)
FoundProducts.Add foundProduct
Else
End If
Next i
Call clearKAndL
Call foundProductsToNewSheet
Exit Sub
ShouldSkip:
skip = True
GoTo CheckSkip
End Sub
 
Hello,

It's currently set to "Break on unhandled errors"

Maybe I should just do some kinda test to see if the object exists within
the collection already, it's just the java equivlent of if(object!=null)
doesn't seem to be around in VBA.
 
I would try replacing "GoTo CheckSkip" with "Resume CheckSkip"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"NateBuckley" <
wrote in message
Hello I have a On Error Goto statement, yet it only works once, then It just
ignores and tries to execute although this causes it to crash.

I'm attempting to grab stuff from a collection
Set tmp = lookUpCollection.Item("Monkey"), there is no object with the key
monkey, and it bypasses it on the first go by following my error handling line

On Error Goto ShouldSkip
The ShouldSkip label contains the following

ShouldSkip:
skip = True
GoTo CheckSkip

Why is it only doing it once, I've used this method before and it worked
fine, just all of a suddent I'm getting the error
Invalid Procedure Call or argument.
Thanks in advance.
 
Any form of "Resume" (in an error handler) clears the error and enables error handling again.
'--
Jim Cone

"NateBuckley"
wrote in message
Cheers Mate that one little change fixed it all,
Why is that?
Thank you!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

on error 2
On Error action inconsistent 2
Can't delete sheets 8
help with on error stuff 2
why doesn't on error work? 7
Addin Auto_Open code executes only in debug mode 2
"On error" within While loop failing 2
Please help with On Error GoTo 3

Back
Top