Excel – How to get a cell’s position within a range

excelvba

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 Row– and 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?

Best Solution

updated using variant provided by lori_m

But I wonder if there are any native functions ...

use this

Sub test()
    Dim r As Range, c As Range
    With Sheet1
        Set r = .[B2:E10]
        Set c = .[C2]
    End With
    If Not Intersect(r, c) Is Nothing Then
        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
Related Question