Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
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 On Mar 27, 10:54*am, NateBuckley wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
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 "NateBuckley" skrev i meddelelsen ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
Cheers Mate that one little change fixed it all,
Why is that? Thank you! "Jim Cone" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error - Only executing once.
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! "Jim Cone" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop error code from executing | Excel Programming | |||
Error when executing 'Charts.Add' command | Excel Programming | |||
File Already Open Error When Executing A Macro | Excel Programming | |||
File Already Open Error When Executing A Macro | Excel Programming |