Results 1 to 8 of 8

Thread: Parameterized use of the match function
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Parameterized use of the match function

    I am trying to make the following function call w/ MATCH

    found = WorksheetFunction.Match(num_months, lookupRC(1), 0)
    where lookupRC(1) is a string holding the named range that I need to use.

    How can I represent this named range in such a way the MATCH function will accept it as the array_value discused in the MS help dialog.

  2. #2
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Parameterized use of the match function

    Perhaps

    Code:
    found = WorksheetFunction.Match(num_months, Range(lookupRC(1)), 0)
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Nov 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameterized use of the match function

    I have tried to use the lookupRC(1) variable in the RANGE function but I keep getting an error saying the match function can't read the array_value parameted. I have also tried to convert the named range into RC cordinates as a quoted string with no luck.... I have also tried "ThisWorkbook.Names(lookupS(0)).RefersToRange" to make a rang but have not been able to get this to work either..

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameterized use of the match function

    Are you sure you don't have it backwards? This works fine for me:

    Code:
    MsgBox WorksheetFunction.Match( _
        Range("foobar")(1, 1).Value, Range("A1:A10"), 0)

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Parameterized use of the match function

    Is lookupRC(1) just the name of the range you want to use in the lookup?
    If posting code please use code tags.

  6. #6
    New Member
    Join Date
    Nov 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameterized use of the match function

    LookupRC(1) holds the text representation of the named range to be used. I have several table that may be used. I have a function that returns 1) the named range of the table 2) the named range of the column to be searched. These items are stored in a 2 element array LookupRC.

    If the tabel to be used is "month_6" then LookupRC(1) has the value month_6 (with out surounding quotes).

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Parameterized use of the match function

    So you have it backwards? Flip the first and second argument.

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Parameterized use of the match function

    The code VoG posted worked fine for me with a one dimensional array LookupRC where LookupRC(1) = "month_6", where month_6 was a named range with one column.

    What are the dimensions of the array?

    What are the dimensions of the range(s)?

    A range used in MATCH can only be one column wide

    Also what's the error message?

    I've never heard of of an error message like the one you mention.

    If there's a problem with the named range the error is Unable to get the Match property... unless you use Application.Match.
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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