+ Reply to Thread
Results 1 to 6 of 6

on error goto trouble

  1. #1
    Gixxer_J_97
    Guest

    on error goto trouble

    Hi all,

    i have the following vba code:

    <BEGIN VBA CODE>
    Dim searchRange As Range, foundRange1 As Range, foundRange2 As Range,
    addRange As Range, prevRange As Range
    Dim numberOfProducts As Integer, position As Integer
    Dim whsName As String, addCodeBox1 As String, addLotBox1 As String

    On Error GoTo isAfter
    Set prevRange = addRange
    Set addRange =
    foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    Range(foundRange1, foundRange2), 0), 0)
    GoTo continue
    setRange:
    Set prevRange = addRange
    Set addRange = foundRange1
    GoTo continue

    isAfter:
    On Error GoTo 0
    On Error GoTo isBefore
    Set addRange =
    foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    Range(foundRange1, foundRange2), -1) - 1, 0)
    On Error GoTo 0
    Resume continue

    isBefore:
    Set addRange =
    foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    Range(foundRange1, foundRange2), 0), 0)
    On Error GoTo 0
    Resume continue
    <END VBA CODE>

    what this code is suppsed to do is set the addRange to the location where i
    need to insert my line in my sheet. it looks in the found range
    (foundRange1:foundRange2)
    using the Match function. the first test is using match(0), to see if there
    is an exact match. if that errors out, then try the isBefore version
    match(-1) version, and if that errors out then try the isAfter version

    when there is nothing in the list it runs fine and puts everything where it
    is supposed to.
    on the second run through, if the code# that is being looked for is 'less
    than' the code# that is there, it will error on the first set addRange and
    goto isAfter like it is supposed to.
    then i will get an error message :
    Run-Time error '1004':
    Unable to get the Match property of the WorksheetFunction class

    this error is on the 3rd line in isAfter: (the set addRange)
    isn't the 'on error goto' supposed to handle this and goto isBefore?
    (if i move the cursor to the first line in isBefore, the code will run ok
    and everything goes where it should.

    what am i missing about error trapping?

    (btw, if the code# that is being matched is 'greater' than the code# that
    exists, the code works ok w/o any VB errors - the error trapping seems to
    work.)

  2. #2
    Patrick Molloy
    Guest

    RE: on error goto trouble

    IMHO its better to use a "safe" function than to trap errors this way

    eg

    insead of

    ON ERROR GOTO ERROR_HANDLER
    '''' code
    ''' more code

    ON ERROR RESUME NEXT
    matchnum = worksheetsunction.Match(what,where,false)
    IF ERR.NUMBER = 0 THEN
    ' OK
    ELSE
    ' No Match - error trapped
    ERR.CLEAR
    END IF
    ' reset the error handler
    ON ERROR GOTO ERROR_HANDLER


    The following is a nicer solution:

    ON ERROR GOTO ERROR_HANDLER
    '''' code
    ''' more code
    matchnum = SafeMatch(what, where)
    IF matchnum = 0 THEN
    ' etc

    no need to break error handlers in your main code

    Private Function SafeMatch(what as string, where as range) as long
    On Error Resume Next
    SafeMatch = worksheetsunction.Match(what,where,false)
    On Error Goto 0
    End Function


    Also, and its contentious, but you should avoid using GOTO - and no, lets
    not get into another long thread on this




    "Gixxer_J_97" wrote:

    > Hi all,
    >
    > i have the following vba code:
    >
    > <BEGIN VBA CODE>
    > Dim searchRange As Range, foundRange1 As Range, foundRange2 As Range,
    > addRange As Range, prevRange As Range
    > Dim numberOfProducts As Integer, position As Integer
    > Dim whsName As String, addCodeBox1 As String, addLotBox1 As String
    >
    > On Error GoTo isAfter
    > Set prevRange = addRange
    > Set addRange =
    > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > Range(foundRange1, foundRange2), 0), 0)
    > GoTo continue
    > setRange:
    > Set prevRange = addRange
    > Set addRange = foundRange1
    > GoTo continue
    >
    > isAfter:
    > On Error GoTo 0
    > On Error GoTo isBefore
    > Set addRange =
    > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > Range(foundRange1, foundRange2), -1) - 1, 0)
    > On Error GoTo 0
    > Resume continue
    >
    > isBefore:
    > Set addRange =
    > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > Range(foundRange1, foundRange2), 0), 0)
    > On Error GoTo 0
    > Resume continue
    > <END VBA CODE>
    >
    > what this code is suppsed to do is set the addRange to the location where i
    > need to insert my line in my sheet. it looks in the found range
    > (foundRange1:foundRange2)
    > using the Match function. the first test is using match(0), to see if there
    > is an exact match. if that errors out, then try the isBefore version
    > match(-1) version, and if that errors out then try the isAfter version
    >
    > when there is nothing in the list it runs fine and puts everything where it
    > is supposed to.
    > on the second run through, if the code# that is being looked for is 'less
    > than' the code# that is there, it will error on the first set addRange and
    > goto isAfter like it is supposed to.
    > then i will get an error message :
    > Run-Time error '1004':
    > Unable to get the Match property of the WorksheetFunction class
    >
    > this error is on the 3rd line in isAfter: (the set addRange)
    > isn't the 'on error goto' supposed to handle this and goto isBefore?
    > (if i move the cursor to the first line in isBefore, the code will run ok
    > and everything goes where it should.
    >
    > what am i missing about error trapping?
    >
    > (btw, if the code# that is being matched is 'greater' than the code# that
    > exists, the code works ok w/o any VB errors - the error trapping seems to
    > work.)


  3. #3
    Dave Peterson
    Guest

    Re: on error goto trouble

    Another way is to drop the .worksheetfunction and use application.match. It
    returns an error value that you can test.

    dim res as Variant 'can be an error
    res = application.match(addcodebox1, range(foundrange1,foundrange2),0)
    'now check the error
    if iserror(res) then
    'do what you want with the error
    else
    set addRange = foundrange1.offset(res,0)
    end if

    Gixxer_J_97 wrote:
    >
    > Hi all,
    >
    > i have the following vba code:
    >
    > <BEGIN VBA CODE>
    > Dim searchRange As Range, foundRange1 As Range, foundRange2 As Range,
    > addRange As Range, prevRange As Range
    > Dim numberOfProducts As Integer, position As Integer
    > Dim whsName As String, addCodeBox1 As String, addLotBox1 As String
    >
    > On Error GoTo isAfter
    > Set prevRange = addRange
    > Set addRange =
    > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > Range(foundRange1, foundRange2), 0), 0)
    > GoTo continue
    > setRange:
    > Set prevRange = addRange
    > Set addRange = foundRange1
    > GoTo continue
    >
    > isAfter:
    > On Error GoTo 0
    > On Error GoTo isBefore
    > Set addRange =
    > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > Range(foundRange1, foundRange2), -1) - 1, 0)
    > On Error GoTo 0
    > Resume continue
    >
    > isBefore:
    > Set addRange =
    > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > Range(foundRange1, foundRange2), 0), 0)
    > On Error GoTo 0
    > Resume continue
    > <END VBA CODE>
    >
    > what this code is suppsed to do is set the addRange to the location where i
    > need to insert my line in my sheet. it looks in the found range
    > (foundRange1:foundRange2)
    > using the Match function. the first test is using match(0), to see if there
    > is an exact match. if that errors out, then try the isBefore version
    > match(-1) version, and if that errors out then try the isAfter version
    >
    > when there is nothing in the list it runs fine and puts everything where it
    > is supposed to.
    > on the second run through, if the code# that is being looked for is 'less
    > than' the code# that is there, it will error on the first set addRange and
    > goto isAfter like it is supposed to.
    > then i will get an error message :
    > Run-Time error '1004':
    > Unable to get the Match property of the WorksheetFunction class
    >
    > this error is on the 3rd line in isAfter: (the set addRange)
    > isn't the 'on error goto' supposed to handle this and goto isBefore?
    > (if i move the cursor to the first line in isBefore, the code will run ok
    > and everything goes where it should.
    >
    > what am i missing about error trapping?
    >
    > (btw, if the code# that is being matched is 'greater' than the code# that
    > exists, the code works ok w/o any VB errors - the error trapping seems to
    > work.)


    --

    Dave Peterson

  4. #4
    Gixxer_J_97
    Guest

    RE: on error goto trouble

    Hi Patrick,

    I see your point. However, I don't think that will work in my case - I
    tried it and i still get the RunTime Error '1004' (maybe i am doing something
    wrong still)
    i don't know if it makes a difference, but my 'codes' are not numbers, they
    are a mix of letters and numbers, but always XXYYY or XXYYY/1, where X is a
    letter and Y is a number.

    i do have break on unhandled erros only checked in the options

    would i be better off adding my code and the line of information to the last
    spot, and then using the sort feature of excel to sort it for me?
    "Patrick Molloy" wrote:

    > IMHO its better to use a "safe" function than to trap errors this way
    >
    > eg
    >
    > insead of
    >
    > ON ERROR GOTO ERROR_HANDLER
    > '''' code
    > ''' more code
    >
    > ON ERROR RESUME NEXT
    > matchnum = worksheetsunction.Match(what,where,false)
    > IF ERR.NUMBER = 0 THEN
    > ' OK
    > ELSE
    > ' No Match - error trapped
    > ERR.CLEAR
    > END IF
    > ' reset the error handler
    > ON ERROR GOTO ERROR_HANDLER
    >
    >
    > The following is a nicer solution:
    >
    > ON ERROR GOTO ERROR_HANDLER
    > '''' code
    > ''' more code
    > matchnum = SafeMatch(what, where)
    > IF matchnum = 0 THEN
    > ' etc
    >
    > no need to break error handlers in your main code
    >
    > Private Function SafeMatch(what as string, where as range) as long
    > On Error Resume Next
    > SafeMatch = worksheetsunction.Match(what,where,false)
    > On Error Goto 0
    > End Function
    >
    >
    > Also, and its contentious, but you should avoid using GOTO - and no, lets
    > not get into another long thread on this
    >
    >
    >
    >
    > "Gixxer_J_97" wrote:
    >
    > > Hi all,
    > >
    > > i have the following vba code:
    > >
    > > <BEGIN VBA CODE>
    > > Dim searchRange As Range, foundRange1 As Range, foundRange2 As Range,
    > > addRange As Range, prevRange As Range
    > > Dim numberOfProducts As Integer, position As Integer
    > > Dim whsName As String, addCodeBox1 As String, addLotBox1 As String
    > >
    > > On Error GoTo isAfter
    > > Set prevRange = addRange
    > > Set addRange =
    > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > Range(foundRange1, foundRange2), 0), 0)
    > > GoTo continue
    > > setRange:
    > > Set prevRange = addRange
    > > Set addRange = foundRange1
    > > GoTo continue
    > >
    > > isAfter:
    > > On Error GoTo 0
    > > On Error GoTo isBefore
    > > Set addRange =
    > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > Range(foundRange1, foundRange2), -1) - 1, 0)
    > > On Error GoTo 0
    > > Resume continue
    > >
    > > isBefore:
    > > Set addRange =
    > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > Range(foundRange1, foundRange2), 0), 0)
    > > On Error GoTo 0
    > > Resume continue
    > > <END VBA CODE>
    > >
    > > what this code is suppsed to do is set the addRange to the location where i
    > > need to insert my line in my sheet. it looks in the found range
    > > (foundRange1:foundRange2)
    > > using the Match function. the first test is using match(0), to see if there
    > > is an exact match. if that errors out, then try the isBefore version
    > > match(-1) version, and if that errors out then try the isAfter version
    > >
    > > when there is nothing in the list it runs fine and puts everything where it
    > > is supposed to.
    > > on the second run through, if the code# that is being looked for is 'less
    > > than' the code# that is there, it will error on the first set addRange and
    > > goto isAfter like it is supposed to.
    > > then i will get an error message :
    > > Run-Time error '1004':
    > > Unable to get the Match property of the WorksheetFunction class
    > >
    > > this error is on the 3rd line in isAfter: (the set addRange)
    > > isn't the 'on error goto' supposed to handle this and goto isBefore?
    > > (if i move the cursor to the first line in isBefore, the code will run ok
    > > and everything goes where it should.
    > >
    > > what am i missing about error trapping?
    > >
    > > (btw, if the code# that is being matched is 'greater' than the code# that
    > > exists, the code works ok w/o any VB errors - the error trapping seems to
    > > work.)


  5. #5
    Gixxer_J_97
    Guest

    Re: on error goto trouble

    thanks to all, the application.match seemed to work the best, but let me run
    this by you...

    is there a better way to find where the code# is supposed to go and insert it?

    i tried inserting after the last code# and then doing a sort, but i have
    formulas down the row that get messed up - absolute column, relative row -
    and they need to be relative rows - which is why i wanted to figure out where
    to put the new code# first before doing anything else.

    thanks!

    J

    again the code numbers are of the format XXYYY or XXYYY/1 where X is a
    letter and Y is a number (ie FC112, FL110, FL104/1, CO123, VE144, RU140)

    "Dave Peterson" wrote:

    > Another way is to drop the .worksheetfunction and use application.match. It
    > returns an error value that you can test.
    >
    > dim res as Variant 'can be an error
    > res = application.match(addcodebox1, range(foundrange1,foundrange2),0)
    > 'now check the error
    > if iserror(res) then
    > 'do what you want with the error
    > else
    > set addRange = foundrange1.offset(res,0)
    > end if
    >
    > Gixxer_J_97 wrote:
    > >
    > > Hi all,
    > >
    > > i have the following vba code:
    > >
    > > <BEGIN VBA CODE>
    > > Dim searchRange As Range, foundRange1 As Range, foundRange2 As Range,
    > > addRange As Range, prevRange As Range
    > > Dim numberOfProducts As Integer, position As Integer
    > > Dim whsName As String, addCodeBox1 As String, addLotBox1 As String
    > >
    > > On Error GoTo isAfter
    > > Set prevRange = addRange
    > > Set addRange =
    > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > Range(foundRange1, foundRange2), 0), 0)
    > > GoTo continue
    > > setRange:
    > > Set prevRange = addRange
    > > Set addRange = foundRange1
    > > GoTo continue
    > >
    > > isAfter:
    > > On Error GoTo 0
    > > On Error GoTo isBefore
    > > Set addRange =
    > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > Range(foundRange1, foundRange2), -1) - 1, 0)
    > > On Error GoTo 0
    > > Resume continue
    > >
    > > isBefore:
    > > Set addRange =
    > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > Range(foundRange1, foundRange2), 0), 0)
    > > On Error GoTo 0
    > > Resume continue
    > > <END VBA CODE>
    > >
    > > what this code is suppsed to do is set the addRange to the location where i
    > > need to insert my line in my sheet. it looks in the found range
    > > (foundRange1:foundRange2)
    > > using the Match function. the first test is using match(0), to see if there
    > > is an exact match. if that errors out, then try the isBefore version
    > > match(-1) version, and if that errors out then try the isAfter version
    > >
    > > when there is nothing in the list it runs fine and puts everything where it
    > > is supposed to.
    > > on the second run through, if the code# that is being looked for is 'less
    > > than' the code# that is there, it will error on the first set addRange and
    > > goto isAfter like it is supposed to.
    > > then i will get an error message :
    > > Run-Time error '1004':
    > > Unable to get the Match property of the WorksheetFunction class
    > >
    > > this error is on the 3rd line in isAfter: (the set addRange)
    > > isn't the 'on error goto' supposed to handle this and goto isBefore?
    > > (if i move the cursor to the first line in isBefore, the code will run ok
    > > and everything goes where it should.
    > >
    > > what am i missing about error trapping?
    > >
    > > (btw, if the code# that is being matched is 'greater' than the code# that
    > > exists, the code works ok w/o any VB errors - the error trapping seems to
    > > work.)

    >
    > --
    >
    > Dave Peterson
    >


  6. #6
    Patrick Molloy
    Guest

    RE: on error goto trouble

    match returns a number, or an error if theres no match.

    "Gixxer_J_97" wrote:

    > Hi Patrick,
    >
    > I see your point. However, I don't think that will work in my case - I
    > tried it and i still get the RunTime Error '1004' (maybe i am doing something
    > wrong still)
    > i don't know if it makes a difference, but my 'codes' are not numbers, they
    > are a mix of letters and numbers, but always XXYYY or XXYYY/1, where X is a
    > letter and Y is a number.
    >
    > i do have break on unhandled erros only checked in the options
    >
    > would i be better off adding my code and the line of information to the last
    > spot, and then using the sort feature of excel to sort it for me?
    > "Patrick Molloy" wrote:
    >
    > > IMHO its better to use a "safe" function than to trap errors this way
    > >
    > > eg
    > >
    > > insead of
    > >
    > > ON ERROR GOTO ERROR_HANDLER
    > > '''' code
    > > ''' more code
    > >
    > > ON ERROR RESUME NEXT
    > > matchnum = worksheetsunction.Match(what,where,false)
    > > IF ERR.NUMBER = 0 THEN
    > > ' OK
    > > ELSE
    > > ' No Match - error trapped
    > > ERR.CLEAR
    > > END IF
    > > ' reset the error handler
    > > ON ERROR GOTO ERROR_HANDLER
    > >
    > >
    > > The following is a nicer solution:
    > >
    > > ON ERROR GOTO ERROR_HANDLER
    > > '''' code
    > > ''' more code
    > > matchnum = SafeMatch(what, where)
    > > IF matchnum = 0 THEN
    > > ' etc
    > >
    > > no need to break error handlers in your main code
    > >
    > > Private Function SafeMatch(what as string, where as range) as long
    > > On Error Resume Next
    > > SafeMatch = worksheetsunction.Match(what,where,false)
    > > On Error Goto 0
    > > End Function
    > >
    > >
    > > Also, and its contentious, but you should avoid using GOTO - and no, lets
    > > not get into another long thread on this
    > >
    > >
    > >
    > >
    > > "Gixxer_J_97" wrote:
    > >
    > > > Hi all,
    > > >
    > > > i have the following vba code:
    > > >
    > > > <BEGIN VBA CODE>
    > > > Dim searchRange As Range, foundRange1 As Range, foundRange2 As Range,
    > > > addRange As Range, prevRange As Range
    > > > Dim numberOfProducts As Integer, position As Integer
    > > > Dim whsName As String, addCodeBox1 As String, addLotBox1 As String
    > > >
    > > > On Error GoTo isAfter
    > > > Set prevRange = addRange
    > > > Set addRange =
    > > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > > Range(foundRange1, foundRange2), 0), 0)
    > > > GoTo continue
    > > > setRange:
    > > > Set prevRange = addRange
    > > > Set addRange = foundRange1
    > > > GoTo continue
    > > >
    > > > isAfter:
    > > > On Error GoTo 0
    > > > On Error GoTo isBefore
    > > > Set addRange =
    > > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > > Range(foundRange1, foundRange2), -1) - 1, 0)
    > > > On Error GoTo 0
    > > > Resume continue
    > > >
    > > > isBefore:
    > > > Set addRange =
    > > > foundRange1.Offset(Application.WorksheetFunction.Match(addCodeBox1,
    > > > Range(foundRange1, foundRange2), 0), 0)
    > > > On Error GoTo 0
    > > > Resume continue
    > > > <END VBA CODE>
    > > >
    > > > what this code is suppsed to do is set the addRange to the location where i
    > > > need to insert my line in my sheet. it looks in the found range
    > > > (foundRange1:foundRange2)
    > > > using the Match function. the first test is using match(0), to see if there
    > > > is an exact match. if that errors out, then try the isBefore version
    > > > match(-1) version, and if that errors out then try the isAfter version
    > > >
    > > > when there is nothing in the list it runs fine and puts everything where it
    > > > is supposed to.
    > > > on the second run through, if the code# that is being looked for is 'less
    > > > than' the code# that is there, it will error on the first set addRange and
    > > > goto isAfter like it is supposed to.
    > > > then i will get an error message :
    > > > Run-Time error '1004':
    > > > Unable to get the Match property of the WorksheetFunction class
    > > >
    > > > this error is on the 3rd line in isAfter: (the set addRange)
    > > > isn't the 'on error goto' supposed to handle this and goto isBefore?
    > > > (if i move the cursor to the first line in isBefore, the code will run ok
    > > > and everything goes where it should.
    > > >
    > > > what am i missing about error trapping?
    > > >
    > > > (btw, if the code# that is being matched is 'greater' than the code# that
    > > > exists, the code works ok w/o any VB errors - the error trapping seems to
    > > > work.)


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1