Refer to Excel cell in Table by header name and row number

Question

I'm trying to refer to a cell in an excel table by using the table header name and the row number using VBA.

How can I do this?

Answer 1

In your example, something like this:

Dim tb As ListObject
'assumes Table is the first one on the ActiveSheet
Set tb = ActiveSheet.ListObjects(1)
MsgBox tb.DataBodyRange.Cells(2, tb.ListColumns("header4").Index)

Answer 2

A shorter answer is:

MsgBox [MyTable].Cells(2, [MyTable[MyColumn]].Column)

Much cleaner and easier!

Answer 3

Is there any reason one should avoid using this method?

ThisWorkbook.Worksheets("MyWksht").Range("TableName[ColumnTitle]").Cells(RowNumber)

Seems the simplest answer in my opinion.