Finding Row Numbers Within the RangeName

[Goofy]

Im a bit confused about this. I have a RangeName Table. I want to Find the
row which say "Blue" is in ( Relative to the range ) so

myRangeName.Find("Green") ' returns a range absolute to the spreadsheet,
this is no good to
me because I want to refer to the table using the Cells property. such as
..Cells('FoundRowNum,9) to get the adjacent value to Green. Whats the
simplest way to do this ?

Activity LookupValue
Blue 7
Green 9
Red 12

 

[Guest]

Try a diferent approach:
Dim r As Range
Set r = MyRange.Find('Whatever)
MsgBox r.Offset(,1)

 

[Goofy]

OK, Lateral thinking is good, but what If I have the following scenario ?
When I find(2) I will return column ?



Col1 Col2
1 2
2 3
3 6
4 5

 

[Guest]

I'm not sure what you're asking here. If you're worried about what column you
find the data in then limit the search
Set r = MyRange.Columns(1).Find('Whatever)

 

[Goofy]

Thats fine, I found you could alter the search by columns or row, so thats
solved it

Cheers