+ Reply to Thread
Results 1 to 7 of 7

MATCH function with a variable as lookup_value?

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    MATCH function with a variable as lookup_value?

    Hi,

    I want to use the MATCH(lookup_value, lookup_array, [match_type]) function in VBA.
    I need the lookup_value to be a variable (dim as long), but I can´t get this to work, see below. It works fine when I use a number as lookup_value, but not a variable.

    Please Login or Register  to view this content.
    I get run-time-error 1004 "Unable to get the MATCH property of the WorksheetFunction class"
    Any idea why I can´t use the variable Vendor as lookup_value in the MATCH function?
    Last edited by mccalle; 02-23-2013 at 07:40 AM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: MATCH function with a variable as lookup_value?

    Are the cells in the range Range("a1:a90") formatted as text?
    Martin

  3. #3
    Registered User
    Join Date
    02-22-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: MATCH function with a variable as lookup_value?

    No. The cells A1:A90 are formatted as General.
    When I exchange the variable Vendor to any number, the MATCH function works.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: MATCH function with a variable as lookup_value?

    Are you certain the values in A1:A90 are numbers and not text? You state that the first of these two statements (using "8077") works, where the 2nd (using 8077) does not.

    TESTO = Application.WorksheetFunction.Match("8077", Sheets("Sammanställning").Range("a1:a90"), 0) 'DOES WORK
    TESTO = Application.WorksheetFunction.Match(8077, Sheets("Sammanställning").Range("a1:a90"), 0) 'DOES NOT WORK
    "8077" (in quotes) is the text string 8077. 8077 (without quotes) is the number (long/integer) 8077. It would appear from these two statements that the match function is not having trouble finding the text string 8077, but cannot find the number/long/integer 8077. I would suggest you double check the data type of your vendor variable and the data type of the values in the A1:A90 and make sure that they all are of the same data type.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    02-22-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: MATCH function with a variable as lookup_value?

    I thought you had found the issue (might still be the issue though) but I´ve changed A1:A90 to number format and all other numbers in the Excel file to number format as well.
    Strangely enough I still get the same error message, i.e.

    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: MATCH function with a variable as lookup_value?

    I´ve changed A1:A90 to number format and all other numbers in the Excel file to number format as well.
    Changing the number format of a cell does not (usually) change the actual value of the cell. A few tests I would suggest:

    1) in a blank column, put the formula =isnumber(a1) and copy down. TRUE indicates that the "number" in A1 is an actual number. FALSE indicates that the "number" in A1 is a text string that only looks like a number.
    2) select a cell from A1:A90 and, in the VBA immediate window, execute a statement like debug.print typename(sheets("...").range("A34").value) and see if it returns double or string or other.

    If any of these kind of tests indicate that the values in A1:a90 are text strings rather than numbers, you will need to either convert them to numbers or change your variable type to string.
    Last edited by MrShorty; 02-22-2013 at 06:06 PM.

  7. #7
    Registered User
    Join Date
    02-22-2013
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: MATCH function with a variable as lookup_value?

    Thanks a lot MrShorty. I tried the Isnumber formula and it turned out A1:A90 were not numbers , they only looked like numbers (at least to me). There was an apostrophe in front of all numbers, which I guess was a non wanted heritage from the database extraction. I´ll definately pay more attention to the data types from now on.

+ 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