Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Application,worksheet-function MATCH in VBA

Status
Not open for further replies.

Crookshanks

Technical User
May 18, 2004
296
NL
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.
 
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? [ponder]
How do I get the best answers?
 
Hello,

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

Regards,
 
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? [ponder]
How do I get the best answers?
 
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top