MATCH function with a variable as lookup_value?

**mccalle** · 02-22-2013, 03:28 PM

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?

**mrice** · 02-22-2013, 03:34 PM

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

**mccalle** · 02-22-2013, 03:48 PM

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

**MrShorty** · 02-22-2013, 04:10 PM

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.

**mccalle** · 02-22-2013, 04:47 PM

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.

**MrShorty** · 02-22-2013, 06:02 PM

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.

**mccalle** · 02-23-2013, 07:37 AM

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.