excel - How do I get a cell's position within a range? -

how go getting relative position of cell within range? finding position of cell in worksheet trivial, using row- , column-properties, unsure of how same within range.

i considered using position of top-left cell in range want find position of cell in, , deduct (-1) position of cell in worksheet, gets little bit cumbersome. there more elegant way go this?

my best attempt, including test, far this:

option explicit  sub test()   dim r range: set r = sheet1.range("b2:e10")   dim c range: set c = sheet1.range("c2")    debug.print "column in sheet: " & c.column   debug.print "row in sheet: " & c.row   debug.print "column in range: " & column_in_range(r, c)   debug.print "row in range: " & row_in_range(r, c) end sub  function column_in_range(r range, c range) long   column_in_range = c.column - (r.cells(1, 1).column - 1) end function  function row_in_range(r range, c range) long   row_in_range = c.row - (r.cells(1, 1).row - 1) end function 

this gives desired output:

column in sheet: 3 row in sheet: 2 column in range: 2 row in range: 1 

but wonder if there native functions can use instead?

updated using variant provided by lori_m

but wonder if there native functions ...

use this

sub test()     dim r range, c range     sheet1         set r = .[b2:e10]         set c = .[c2]     end     if not intersect(r, c) nothing         debug.print "column in sheet: " & c.column         debug.print "row in sheet: " & c.row         debug.print "column in range: " & range(r(1), c).columns.count         debug.print "row in range: " & range(r(1), c).rows.count     end if end sub 

output

column in sheet: 3 row in sheet: 2 column in range: 2 row in range: 1