# Determine Target position within named range

• ### Question

• Hi all,

I have a worksheet change event and I refers to a named range or around 50 Cells, lets call it "ThisRange"

I know when I want to write to the 4th cell in this range I can simply write ThisRange(4) = ......

However what I want to read is which cell the current target cell is. i.e. when I select a cell in ThisRange I would like to know if it is cell 4 within that named range as above or any other cell, so that I can use its position to action something in the correct psoition within another named range.

Can this numeric position be easily read?

Monday, September 3, 2012 2:30 PM

• See if below helps...

Function TellMyPostion(rParent As Range, rChild As Range) As Long

Dim rFirstCell As Range

Dim lColOffset As Long
Dim lRowOffset As Long
Dim lColumns As Long

If Application.Intersect(rParent, rChild) Is Nothing Then
TellMyPostion = 0
Exit Function
End If

Set rFirstCell = rParent(1)

lColumns = rParent.Columns.Count
lColOffset = rChild.Column - rFirstCell.Column + 1
lRowOffset = rChild.Row - rFirstCell.Row + 1

TellMyPostion = lColOffset + (lRowOffset - 1) * lColumns

End Function

Best Regards,
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

• Marked as answer by Monday, September 3, 2012 3:08 PM
Monday, September 3, 2012 2:55 PM
• Hey Jive-Bunny,

I'd approach this problem by finding the position of the first and last cells in the range, and the current cell which the cursor is sitting in (with activecell), then comparing them.

Monday, September 3, 2012 2:33 PM

### All replies

• Hey Jive-Bunny,

I'd approach this problem by finding the position of the first and last cells in the range, and the current cell which the cursor is sitting in (with activecell), then comparing them.

Monday, September 3, 2012 2:33 PM
• Hi Ben,

Thank you for the quick response.  That will work and easily too as they are in a single column so I can use;

ThisRangeCellIndex = 1 + Target.Row - ThisRange(1).Row

Where I have previously defined "ThisRange" as a range and "ThisRangeCellIndex" as an integer

I was just hoping to have a more universal method thay I can then re use in all scenarios in the future.

Does anyone know if the inverse of ThisRange(4) exists so that I can read the "4" directly?

Monday, September 3, 2012 2:40 PM
• See if below helps...

Function TellMyPostion(rParent As Range, rChild As Range) As Long

Dim rFirstCell As Range

Dim lColOffset As Long
Dim lRowOffset As Long
Dim lColumns As Long

If Application.Intersect(rParent, rChild) Is Nothing Then
TellMyPostion = 0
Exit Function
End If

Set rFirstCell = rParent(1)

lColumns = rParent.Columns.Count
lColOffset = rChild.Column - rFirstCell.Column + 1
lRowOffset = rChild.Row - rFirstCell.Row + 1

TellMyPostion = lColOffset + (lRowOffset - 1) * lColumns

End Function

Best Regards,
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

• Marked as answer by Monday, September 3, 2012 3:08 PM
Monday, September 3, 2012 2:55 PM
• That will do nicely and will handle 2D ranges.

Still surprised an inverse isn't available, but I dont need it now.  Thanks Ben and Thanks L&L

Monday, September 3, 2012 3:10 PM
• Thanks to Asadulla Javed... I too needed to find a way of identifying:
-- the relative position, within a named range (my "main table" in an Excel worksheet),
-- of a single cell, inside that range.

Your function is brilliant and succinct! For people like me who find some VBA code impenetrably algebraic, I have taken your idea and simply re-worked it using nomenclature more readily accessible to me, as below.

I've also added a small procedure (after the function) for testing it, which the same people may find helpful.

Thanks again!

'====================================

Function CellPosition(CellToIdentify As Range, MainTable As Range)
'
' based on an original from Asadulla Javed, Kolkata, found on 2013-10-01 on:
' http://social.msdn.microsoft.com/Forums/en-US/68dcc0ed-59e2-4a51-8a9e-e7d14ab1dc9f/determine-target-position-within-named-range

Dim MainTable_NumberOfColumns As Long
Dim ColumnDifferential As Long
Dim RowDifferential As Long

Set MainTable_FirstCell = MainTable(1)

If Intersect(MainTable, CellToIdentify) Is Nothing Then
CellPosition = 0
Exit Function
End If

MainTable_NumberOfColumns = MainTable.Columns.Count
ColumnDifferential = CellToIdentify.Column - MainTable_FirstCell.Column + 1
RowDifferential = CellToIdentify.Row - MainTable_FirstCell.Row + 1

CellPosition = ColumnDifferential + (RowDifferential - 1) * MainTable_NumberOfColumns
End Function

'====================================

Sub TestCellPosition()

myRangeName = "caltable"

x = CellPosition(ActiveCell, Range(myRangeName))

MsgBox "The position of the active cell, within range <" & myRangeName & ">, is " & x

End Sub

Wednesday, October 2, 2013 10:05 AM