+ Reply to Thread
Results 1 to 6 of 6

Problem w/ Match prop vs. Match method

  1. #1
    George Raft
    Guest

    Problem w/ Match prop vs. Match method

    Wishing all a happy new year ...

    I get a runtime error with this piece of code:

    junk = Application.WorksheetFunction.VLookup("Min", table,
    Application.WorksheetFunction.Match("Gorge", top, 0))

    junk is variant, table and top are ranges.

    The error is:

    "Unable to get the Match Property of the WS function"

    If I replace the ref to App.WSF.Match with an integer, it works fine.

    Thoughts?

    Thanks, Tony



  2. #2
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    One thing to try is doing the two operations separately instead of nesting them. Not sure why this would help, but at least it might help you to debug the Match statement ... which I use a lot and never have a problem with, except when I have not successfully defined the range.

    With the latter thought in mind, try a test such as;

    If top is Nothing then
    Msgbox "range top is not defined"
    Else:
    matchRow = Application.WorksheetFunction.Match("Gorge", top, 0)
    End if

  3. #3
    Dave Peterson
    Guest

    Re: Problem w/ Match prop vs. Match method

    Try dropping the .worksheetfunction from your code:

    with application
    junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    end with

    if iserror(Junk) then
    msgbox "Hey, there wasn't a match"
    else
    msgbox junk
    end if

    =========
    If you want to keep the .worksheetfunction, you'll have to see if there was a
    runtime error.

    with application.worksheetfunction
    on error resume next
    junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    if err.number <> 0 then
    msgbox "Hey, there wasn't a match"
    err.clear
    else
    msgbox junk
    end if
    on error go to 0
    end with

    ===
    By using the with/end with stuff, I could save some typing.

    George Raft wrote:
    >
    > Wishing all a happy new year ...
    >
    > I get a runtime error with this piece of code:
    >
    > junk = Application.WorksheetFunction.VLookup("Min", table,
    > Application.WorksheetFunction.Match("Gorge", top, 0))
    >
    > junk is variant, table and top are ranges.
    >
    > The error is:
    >
    > "Unable to get the Match Property of the WS function"
    >
    > If I replace the ref to App.WSF.Match with an integer, it works fine.
    >
    > Thoughts?
    >
    > Thanks, Tony


    --

    Dave Peterson

  4. #4
    George Raft
    Guest

    Re: Problem w/ Match prop vs. Match method

    Thanks Dave,

    It works and it's more elegant - I'm learning slowly, but I'm learning. But
    why does it work? Or more precisely, why didn't my clumsier version work?
    Is there something fundamental I'm missing?

    Tony

    Dave Peterson <[email protected]> wrote in message
    news:[email protected]...
    > Try dropping the .worksheetfunction from your code:
    >
    > with application
    > junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    > end with
    >
    > if iserror(Junk) then
    > msgbox "Hey, there wasn't a match"
    > else
    > msgbox junk
    > end if
    >
    > =========
    > If you want to keep the .worksheetfunction, you'll have to see if there

    was a
    > runtime error.
    >
    > with application.worksheetfunction
    > on error resume next
    > junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    > if err.number <> 0 then
    > msgbox "Hey, there wasn't a match"
    > err.clear
    > else
    > msgbox junk
    > end if
    > on error go to 0
    > end with
    >
    > ===
    > By using the with/end with stuff, I could save some typing.
    >
    > George Raft wrote:
    > >
    > > Wishing all a happy new year ...
    > >
    > > I get a runtime error with this piece of code:
    > >
    > > junk = Application.WorksheetFunction.VLookup("Min", table,
    > > Application.WorksheetFunction.Match("Gorge", top, 0))
    > >
    > > junk is variant, table and top are ranges.
    > >
    > > The error is:
    > >
    > > "Unable to get the Match Property of the WS function"
    > >
    > > If I replace the ref to App.WSF.Match with an integer, it works fine.
    > >
    > > Thoughts?
    > >
    > > Thanks, Tony

    >
    > --
    >
    > Dave Peterson




  5. #5
    Dave Peterson
    Guest

    Re: Problem w/ Match prop vs. Match method

    Application.worksheetfunction.match() causes a runtime error. About the only
    thing you can do is to turn off the error checking and then check for yourself.

    application.match() returns an error that you can check with iserror().

    It's just one of the vagaries of excel/vba.

    I find the application.match() (and application.vlookup()), prettier???.

    But that's just a personal choice.



    George Raft wrote:
    >
    > Thanks Dave,
    >
    > It works and it's more elegant - I'm learning slowly, but I'm learning. But
    > why does it work? Or more precisely, why didn't my clumsier version work?
    > Is there something fundamental I'm missing?
    >
    > Tony
    >
    > Dave Peterson <[email protected]> wrote in message
    > news:[email protected]...
    > > Try dropping the .worksheetfunction from your code:
    > >
    > > with application
    > > junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    > > end with
    > >
    > > if iserror(Junk) then
    > > msgbox "Hey, there wasn't a match"
    > > else
    > > msgbox junk
    > > end if
    > >
    > > =========
    > > If you want to keep the .worksheetfunction, you'll have to see if there

    > was a
    > > runtime error.
    > >
    > > with application.worksheetfunction
    > > on error resume next
    > > junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    > > if err.number <> 0 then
    > > msgbox "Hey, there wasn't a match"
    > > err.clear
    > > else
    > > msgbox junk
    > > end if
    > > on error go to 0
    > > end with
    > >
    > > ===
    > > By using the with/end with stuff, I could save some typing.
    > >
    > > George Raft wrote:
    > > >
    > > > Wishing all a happy new year ...
    > > >
    > > > I get a runtime error with this piece of code:
    > > >
    > > > junk = Application.WorksheetFunction.VLookup("Min", table,
    > > > Application.WorksheetFunction.Match("Gorge", top, 0))
    > > >
    > > > junk is variant, table and top are ranges.
    > > >
    > > > The error is:
    > > >
    > > > "Unable to get the Match Property of the WS function"
    > > >
    > > > If I replace the ref to App.WSF.Match with an integer, it works fine.
    > > >
    > > > Thoughts?
    > > >
    > > > Thanks, Tony

    > >
    > > --
    > >
    > > Dave Peterson


    --

    Dave Peterson

  6. #6
    George Raft
    Guest

    Re: Problem w/ Match prop vs. Match method

    Thanks again Dave, that helps.

    Tony

    Dave Peterson <[email protected]> wrote in message
    news:[email protected]...
    > Application.worksheetfunction.match() causes a runtime error. About the

    only
    > thing you can do is to turn off the error checking and then check for

    yourself.
    >
    > application.match() returns an error that you can check with iserror().
    >
    > It's just one of the vagaries of excel/vba.
    >
    > I find the application.match() (and application.vlookup()), prettier???.
    >
    > But that's just a personal choice.
    >
    >
    >
    > George Raft wrote:
    > >
    > > Thanks Dave,
    > >
    > > It works and it's more elegant - I'm learning slowly, but I'm learning.

    But
    > > why does it work? Or more precisely, why didn't my clumsier version

    work?
    > > Is there something fundamental I'm missing?
    > >
    > > Tony
    > >
    > > Dave Peterson <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Try dropping the .worksheetfunction from your code:
    > > >
    > > > with application
    > > > junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    > > > end with
    > > >
    > > > if iserror(Junk) then
    > > > msgbox "Hey, there wasn't a match"
    > > > else
    > > > msgbox junk
    > > > end if
    > > >
    > > > =========
    > > > If you want to keep the .worksheetfunction, you'll have to see if

    there
    > > was a
    > > > runtime error.
    > > >
    > > > with application.worksheetfunction
    > > > on error resume next
    > > > junk = .VLookup("Min", table, .Match("Gorge", top, 0))
    > > > if err.number <> 0 then
    > > > msgbox "Hey, there wasn't a match"
    > > > err.clear
    > > > else
    > > > msgbox junk
    > > > end if
    > > > on error go to 0
    > > > end with
    > > >
    > > > ===
    > > > By using the with/end with stuff, I could save some typing.
    > > >
    > > > George Raft wrote:
    > > > >
    > > > > Wishing all a happy new year ...
    > > > >
    > > > > I get a runtime error with this piece of code:
    > > > >
    > > > > junk = Application.WorksheetFunction.VLookup("Min", table,
    > > > > Application.WorksheetFunction.Match("Gorge", top, 0))
    > > > >
    > > > > junk is variant, table and top are ranges.
    > > > >
    > > > > The error is:
    > > > >
    > > > > "Unable to get the Match Property of the WS function"
    > > > >
    > > > > If I replace the ref to App.WSF.Match with an integer, it works

    fine.
    > > > >
    > > > > Thoughts?
    > > > >
    > > > > Thanks, Tony
    > > >
    > > > --
    > > >
    > > > Dave Peterson

    >
    > --
    >
    > Dave Peterson




+ 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