Search for text in name range in Sheet 1and return Value in Sheet 2

[rock.cworld]

Hi,

I have something of the following :

Shee 1 (Name Range : Code)
Column A -------- Column B

School ----------- 1
College ----------- 2
University--------- 3

Sheet 2
Cell A1 = (Search for "College" in Name Range Code and get the Value of "2" as code. ) So value of 2 should be present in Cell A1.

 

[Domski]

Hi,

Try:

=VLOOKUP("College",Code,2,FALSE)

Dom

 

[rock.cworld]

Hi,

Sorry but I forgot to mention that I want it in VBA.

 

[Domski]

Have look at the VBA Find method. It will return a range if it finds a match and you can then use Offset to return the value in the adjacent column.

Dom

 

[rock.cworld]

Can you please explain with code. I tried using VBA Find but could not use it for my example.

Thanks.