Return the row number of a cell relative to a range

[Frederick Chow]

Hi all,

Is it possible to return the location of cell relative to a range, for
example, to return cell B3 as row 1 relative to range A3:B5? I found that
the RangeObj.Row property return only the absolute row number. thanks a lot.

Frederick Chow
Hong Kong.

 

[Doug Glancy]

Frederick,

range("B3").row - (range("B3:c5").Rows(1).row + 1)

hth,

Doug

 

[Frederick Chow]

Dong,

Thanks for your quick response but I don't think your suggestion will work
for both Range("B3").Row and Range("B3:C5").Rows(1).Row return 3....

Frederick Chow

 

[Doug Glancy]

Frederick,

I misplaced a parentheses. Should have been:

range("B3").row - (range("a3:c5").Rows(1).row) + 1

As you say they both return 3. That's why I subtracted one from the other
and added 1. Try typing it into the immediate window (Ctl-G in the VBE):

? range("B3").row - (range("B3:c5").Rows(1).row + 1)

and you'll see that it returns 1, which is what you wanted.

hth,

Doug