VBA Match Equivalent

[Network Admin]

I am in the process of making things calculated by VBA in one of our
Excel projects that has grown. I have a Validation drop down box
containing a list called "Hinges" and if someone chooses hinge "DSEL"
how can I get VBA to return number 4 (which is the location on the
list given by the Match Function). This is crucial to us because we
need that number to look up things in other arrays.

Thanks

 

[Bob Phillips]

Use Match

myNum = WorksheetFunction.Match("DSel",Range("A1:A20"),0)
If myNum = 0 Then
MsgBox "Not found"
End If

 

[Dave Peterson]

With application.worksheetfunction.match, if there is not a match, you'll get a
run time error.

maybe:

dim res as variant
res = application.match("dsel",worksheets("sheet1").range("a1:a20"),0)
if iserror(res) then
'no match
else
msgbox res
end if

Application.match (w/o the worksheetfunction) will return an error that can be
checked later.