1

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).

enter image description here

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
user3601876
  • 47
  • 6

3 Answers3

2

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
user3601876
  • 47
  • 6
1

.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

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • The application.match works perfectly. Still thinking about the .find-approach: If I understand your comment correctly, it is basically not possible to pull something w/in a ListOject as .find refers to ranges which again make reference to the whole worksheet. – user3601876 Jun 03 '21 at 08:21
  • 1
    @user3601876 I would suggest to use `Dim j As Variant`. Only for the case when "Column3" does not match anything in the searched range. Then use `If Not IsError(j) Then MsgBox j`. In the suggested way, it will not rise an error, even without checking using `IsError` it will return "Error 2042", if the string could not be found... – FaneDuru Jun 03 '21 at 08:30
  • Voted it up. I think that the above suggestion can make the `Match` part more versatile... – FaneDuru Jun 03 '21 at 08:31
  • 1
    *".. I want the `j` in my code to be 3 (not 6)"* - As the `.Find` results itself in a *range object* within the given worksheet and its `.Column` property returns the column therein (e.g. 6), you can calculate the wanted column position within the listobject by a subtraction as follows: `6` *minus* the listobject 's start column (`- lo.Range.Cells(1, 1).Column`) plus `+1` (as there is no zero column) :-) @user3601876 – T.M. Jun 03 '21 at 08:50
  • 1
    In addition to @FaneDuru 's valid comment: As the late bound `Application.Match` is expected to return a position number in case something is found, it's also possible to exclude *non-findings* via the following condition: `If IsNumeric(j) Then MsgBox j` instead of negating an error :-) – T.M. Jun 03 '21 at 08:57
  • 1
    @user3601876 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` – Siddharth Rout Jun 03 '21 at 09:16
0

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

enter image description here

Kin Siang
  • 2,644
  • 2
  • 4
  • 8