Error Handling with Worksheetfunction.Match

Discussion in 'Microsoft Excel Programming' started by mikelee101, Jun 1, 2009.

  1. mikelee101

    mikelee101 Guest

    Hello,
    I am having trouble with error handling. Not sure how much of the code to
    include here, as I can’t really tell where the issue is. Here’s the sub
    routine where it’s breaking down:
    ===========
    MoveData:
    On Error GoTo BadProjectName
    ToRowNum = Application.WorksheetFunction.Match(ProjNme,
    ToRng.Columns(1).Cells, 0)

    On Error GoTo IrregularVendor
    ToColNum = Application.WorksheetFunction.Match(VendNme, ToRng.Rows(1).Cells,
    0) ***err on this line***

    On Error GoTo 0
    ToRng.Cells(ToRowNum, ToColNum).Value = Rslt

    IrregularVendor:
    On Error GoTo 0
    Return
    ============
    The problem is that, the second time that it encounters an irregular vendor,
    I get a run time error (Unable to get the Match property of the
    WorksheetFunction class). The fact that it works once leads me to believe
    that some sort of setting is being retained, but I can't figure out what it
    could be. I’ve tried inserting err.clear in various places, with no luck.
    If anyone has any suggestions, please let me know.

    Excel07, XPPro, VB6.5
     
    mikelee101, Jun 1, 2009
    #1
    1. Advertisements

  2. I would just drop the On Error stuff and .worksheetfunction and use:

    Dim ToRowNum as Variant 'not long, it could be an error
    dim ToColNum as variant 'not long

    torownum = application.match(projnme, torng.columns(1), 0)
    tocolnum = application.match(vendnme, torng.rows(1),0)

    if iserror(torownum) _
    or iserror(tocolnum) then
    msgbox "at least one mismatch"
    else
    ToRng.Cells(ToRowNum, ToColNum).Value = Rslt
    end if
     
    Dave Peterson, Jun 1, 2009
    #2
    1. Advertisements

  3. ps. Clear the error (err.clear) and then try Resume Next.

    pps. I like the Variants, using application.match() and checking for errors
    better. I think it's easier to understand.
     
    Dave Peterson, Jun 1, 2009
    #3
  4. MATCH, like VLOOKUP and HLOOKUP functions raise an error when used but
    there's no match

    my preference is to create a UDF to handle these
    With Match, returning a zero would indicate no match

    so
    ToColNum = Application.WorksheetFunction.Match(VendNme, ToRng.Rows(1).Cells,
    would become
    ToColNum = SafeMatch(VendNme, ToRng.Rows(1).Cells)
    If ToColNum=0 then
    ' handle the no match found condition
    end if


    in you module add this
    FUNCTION SafeMatch(what as string, where as range) as long
    on error resume next
    SafeMatch = Application.WorksheetFunction.Match(what,where,false)
    on error goto 0
    END FUNCTION
     
    Patrick Molloy, Jun 2, 2009
    #4
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.