Application,worksheet-function MATCH in VBA

[Crookshanks]

Goodmorning,

I am trying to use a worksheetfunction in VBA to win some time. I want to use the MATCH function to retrieve the row in which a certain item is stored. In this case I don't have to loop through an array.

Function finditem(dataItem)
Dim row DD as variant
rowDD = Application.WorksheetFunction.Match(dataItem, Worksheets("DD").Range("b1:b150"), 0)

I want rowDD to return the rownumber in which dataItem is stored in column B. But the function keeps returning an #value statement in the destination cell of the function. Has anybody an idea what I've done wrong?

Thanks in advance, kind regards.

 

[Loomah]

hi
first thing to check is does "dataItem" actually exist in your list?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Crookshanks]

Hello,

dataItem is a variable (argument) which comes with the function. Is dat incorrect?

Regards,

 

[Loomah]

yeah
if your range b1:b150 contains the numbers 1 to 150 then entering the function finditem(9999) will return #value as 9999 doesn't exist in the range b1:b150.

this is down in part to the way you are using the match fuction. have alook in the help file for a description of the 3rd argument.

one other thing to note is that your function will only return the row number because your data begins in row 1. the match function does not return the row number it returns the reletive position of the item you are trying to match in the list you are checking against.

in other words if your data (nums 1-150) is in b1:b150 finditem(2) will return 2 and this will equate to row 2. however if your data is in the range b100:b149 then finditem(2) will still return 2 despite the row being 101.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Crookshanks]

Thank for you good explanation, I get your point. dataItem is a text value which absolute is somewhere in that range, I will check the docs though.. thanks!