Results 1 to 8 of 8
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MATCH function (excel 97/2000)

    Can you use the MATCH function across workbooks? <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> I was using it from VBA and kept getting errors. The data I'm searching is in one workbook but the data to be found is located in a different workbook.

    Thnx, Deb <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: MATCH function (excel 97/2000)

    In a word, YES!
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH function (excel 97/2000)

    Ok, a few more words please ... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I can get it to work from a function in a cell, but not from VBA. This is a test I did:
    <pre>Public Sub testMatch()
    Dim srcData As String, x

    srcData = Mid(ThisWorkbook.Names("SerialNumbers").RefersTo, 2)
    x = Application.WorksheetFunction.Match("FAA044899AM", srcData, 0)

    End Sub
    </pre>

    The named var "SerialNumbers" is located in the workbook where the code is run, but it points to a range of data in another workbook.

    I get error 1004: Unable to get the Match property of the WorksheetFunction class

    Thnx, Deb <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: MATCH function (excel 97/2000)

    OK, Try:

    Public Sub testMatch()
    Dim srcData as Variant, x
    srcData = ThisWorkbook.ActiveSheet.Range("SerialNumbers")
    x = Application.WorksheetFunction.Match("FAA044899AM", srcData, 0)
    MsgBox x ' just for testing
    End Sub

    Provided you define the "SerialNumbers" range while you're in the workbook where they're named, everything should be fine. Note the change of srcdata from String to Variant - string doesn't work here.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH function (excel 97/2000)

    I copied it exactly and it fails on the first line with error 1004, "Application-defined or object-defined error"

    SerialNumbers is not defined in the workbook which is being searched, it's defined in the workbook where the search string ("FAA044899AM") is located. That's my original problem, trying to get it working when the data to be searched, and the data to be found are not in the same workbook.

    WorkbookA - has the master list of serial numbers (for 2nd part of MATCH)
    WorkbookB - has this code, the named range SerialNumbers, and the data that I'm searching for (the 1st argument to MATCH).

    In WorkbookB, SerialNumbers is defined as: "[WorkbookA.xls]SN_Sheet!$F$2:$F$421"

    I ended up using the Range.Find method but would still like to see if Match works. (This exact data works fine if done with a formula).

    Thnx, Deb <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH function (excel 97/2000)

    ok I got it working now. I re-read the help for MATCH and finally saw what it was telling me. The 2nd arg must be a RANGE. This works:
    <pre>Public Sub testMatch()
    Dim srcData As Variant, x As Variant

    srcData = Mid(ThisWorkbook.Names("SerialNumbers").RefersTo, 3)
    srcData = Left(srcData, Len(srcData) - 1)
    x = Application.Match("FAA0447ACHD", Range(srcData), 0)
    MsgBox x ' just for testing
    End Sub
    </pre>

    Thnx, Deb <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: MATCH function (excel 97/2000)

    I'm glad you sorted that out. If you have a look at the solution I sent, you'll see that I'd interpreted your 'SerialNumbers' as referrring to a range also - precisely because it was the second argument in your match statement!
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MATCH function (excel 97/2000)

    Yes, it is a range, I had mentioned that in all my posts. It's defined as:

    [WorkbookA]SNSheet!$F$2:$f$427

    (this isn't the exact name but you get the idea).

    The problem was that we both were using a string/variant as the 2nd argument not a Range.

    Thnx, Deb

Posting Permissions

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