Looking up the value of a cell in a named range

[Guest]

Hello guys,

I'm have a named range in Worksheet1 which is used back on Worksheet2 (as a
list which from which the user can choose).

Depending on what the user selects I want to perform some calculation, but
that's where my problem starts. For example

my named range contains:
- VB
- VC
- VB+
etc...

Depending on what the user chooses in the list on Worksheet2, i will add 2
numbers if chosen VB, or substract 2 numbers, or multiply numbers...

I've tried If... then, Case Else.. and trying to do something like this:

if SelectedCode = Range("CodesRange").Item Then - which doesn't work OR
for each c in Range("CodesRange") - but this doesn't seem efficient.

Anyone has any suggestions about how i can do this in VBA, as efficiently as
possible?

Thanks in advance!

Memento

 

[Guest]

Why don't you use a find?

set c = Range("CodesRange").find(what:=Selectedcode, _
lookin:xlvalues)
if not c is nothing then