CODEWITHSUNDEEP
vbaexcel
Ayusman
Ayusman

Reputation: 8729

Finding relative row and column number of a range OR Cell

Is it possible to find the relative row and column number of a range or cell within a Range.

Example: In the following Table (Table Name "Table2") Example Table Data

If I use the Find method and print the address like so:

Dim loHeaderRow As Range
Set loHeaderRow = ThisWorkbook.Worksheets("Sheet3").ListObjects("Table2").HeaderRowRange
Dim rFindResult As Range
Set rFindResult = loHeaderRow.Find(What:="MajorVersion", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

MsgBox rFindResult.Address

will Print 

$C$3

But the relative location of the range rFindResult with in the parent range loHeaderRow is:

Row number: 1
Column Number: 2

Can this be achieved? Especially so by using standard object properties/methods without needing a long set of code?

Upvotes: 3

Views: 4382

Answers (2)

L42
L42

Reputation: 19737

Try this:

Sub test2()
    Dim mytable As ListObject
    Set mytable = Sheet1.ListObjects("Table2") '~~> adjust the sheet code name to suit
    Dim myarr: myarr = mytable.Range

    Dim i As Long, j As Long
    Dim mysrch As String

    mysrch = "MajorVersion"
    For i = LBound(myarr, 1) To UBound(myarr, 1)
        For j = LBound(myarr, 2) To UBound(myarr, 2)
            If myarr(i, j) = mysrch Then
                Debug.Print "Relative Row: " & i
                Debug.Print "Relative Column: " & j
                Exit For
            End If
        Next
    Next
End Sub

But if you only need what Sid pointed out in comments, take his route.
Btw, this is just the logic, you can change this into a function if you are to use those row and column numbers.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

Your Row number will always be 1 as you are finding the text in the header row. As for the Column, you can use Debug.Print (rFindResult.Column - loHeaderRow.Column) + 1

For example

Sub Sample()
    Dim loHeaderRow As Range, rFindResult As Range

    Set loHeaderRow = ThisWorkbook.Worksheets("Sheet3").ListObjects("Table2").HeaderRowRange

    Set rFindResult = loHeaderRow.Find(What:="MajorVersion", _
                                       LookIn:=xlValues, _
                                       LookAt:=xlWhole, _
                                       MatchCase:=False, _
                                       SearchFormat:=False)

    MsgBox "The Row is 1 and the Column is " & _
    (rFindResult.Column - loHeaderRow.Column) + 1
End Sub

This will give you 1 and 2 as the row and the column number.

Upvotes: 3

Related Questions