excelvbafindnamed-rangeslistobject

Why FIND returns nothing with structured table?

Hoping someone could shed something on my line of vba script below and point out the error:

Sub findKeyword()

Dim tbl as ListObject

set tbl = activesheet.ListOBjects("myTable") 'containing a header row

set r as range

set r = tbl.ListRows(1).Range.Find("name")

End sub

the Find keeps returning nothing when the header called name does exist in the header row of the structured table. On the other hand, set r = activesheet.rows(1).find("name") is able to find the search text.

Solution

  • ListRows = all the rows of data in the ListObject.

    It's not the range with the headers.

    tbl.Listcolumns("name")

    will give you a reference to the "name" column.