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

Use Match

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

--

HTH

RP

"Network Admin" wrote in message
om...
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

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").rang e("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.



Bob Phillips wrote:

Use Match

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

--

HTH

RP

"Network Admin" wrote in message
om...
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

--

Dave Peterson