VBA: How to retrieve ListObject-Column-Count of given element in HeaderRowRange?

Question

I am trying (and failing) to find a given string in the HeaderRowRange of a ListObject. I would like to pull the ListObject-Column Count (not the spreadsheet-column count). I am trying to address with using ".index", however, it is running into an error. What is wrong with the below code?

See screenshot to understand better my goal: w/in the ListObject Column3 is the 3rd column, while w/in the worksheet it is column 6 (column F). I want the j in my code to be 3 (not 6).

Sub MWE()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("worksheet1")
Dim lo As ListObject: Set lo = ws.ListObjects("table1")
Dim j As Long: j = lo.HeaderRowRange.Find("Column3", LookIn:=xlValues, LookAt:=xlWhole).Index
End Sub

Answer 1

There are several way to access the column in listobject table other than hearderRow, hope you find it useful

Sub tt()

Dim tb1 As ListObject
Dim rcol As Range

Set tb1 = Sheet1.ListObjects("Table1")
Set rcol = tb1.ListColumns("Ctry").DataBodyRange

Debug.Print rcol(3).Value  'Hawaii

End Sub

Answer 2

.Find returns a range object. Is this what you are trying?

Option Explicit

Sub Sample()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("worksheet1")
    Dim lo As ListObject: Set lo = ws.ListObjects("Table1")
    Dim aCell As Range
    Set aCell = lo.HeaderRowRange.Find("Column3", LookIn:=xlValues, LookAt:=xlWhole)

    If Not aCell Is Nothing Then
        MsgBox aCell.Column
    End If
End Sub

Alternatively you can also use Application.Match

Option Explicit

Sub Sample()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets("worksheet1")
    Dim lo As ListObject: Set lo = ws.ListObjects("Table1")
    Dim j As Long: j = Application.Match("Column3", lo.HeaderRowRange, 0)
    
    MsgBox j
End Sub

PS: You also have a typo in your code. Dim lo As listoject should be Dim lo As ListObject

EDIT

See screenshot to understand better my goal: w/in the ListObject Column3 is the 3rd column, while w/in the worksheet it is column 6 (column F). I want the j in my code to be 3 (not 6).

In such a case if your table doesn't start from Col A then you will have to do the range adjustment. Change MsgBox aCell.Column to MsgBox aCell.Column - lo.HeaderRowRange.Column + 1 in the first code.

Output

Answer 3

As I know the name of the column header, I can simply use this code to get the column count w/in the ListObject:

j= lo.ListColumns("Column3").Index