# 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
``````