How would I go about getting the relative position of a cell within a range? Finding the position of a cell in a worksheet is trivial, using the
Column-properties, but I am unsure of how to do the same within a range.
I considered using the position of the top-left cell in the range I want to find the position of a cell in, and just deduct it (-1) from the position of the cell in the worksheet, but it gets a little bit cumbersome. Is there a more elegant way to go about this?
My best attempt, including a test, so far is this:
Option Explicit Sub test() Dim r As Range: Set r = Sheet1.Range("B2:E10") Dim c As 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 As Range, c As Range) As Long column_in_range = c.Column - (r.Cells(1, 1).Column - 1) End Function Function row_in_range(r As Range, c As Range) As Long row_in_range = c.Row - (r.Cells(1, 1).Row - 1) End Function
This gives the desired output:
Column in sheet: 3 Row in sheet: 2 Column in range: 2 Row in range: 1
But I wonder if there are any native functions I can use instead?