none
Determine Target position within named range RRS feed

  • 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

Answers

  • 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,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    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 Antony White Monday, September 3, 2012 3:08 PM
    Monday, September 3, 2012 2:55 PM
    Answerer
  • 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,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    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 Antony White Monday, September 3, 2012 3:08 PM
    Monday, September 3, 2012 2:55 PM
    Answerer
  • 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