CODEWITHSUNDEEP
excelvba
Ole Henrik Skogstrøm
Ole Henrik Skogstrøm

Reputation: 6493

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

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?

enter image description here

Upvotes: 36

Views: 141125

Answers (8)

Ibo
Ibo

Reputation: 4309

It is as simple as this. If you call by the name of the column, then the column index will be 1 for that range so:

msgbox ActiveSheet.ListObjects(1).ListColumns("header4").DataBodyRange(2,1).value

Note that the column index in DataBodyRange is always 1, because there is 1 column in the column you refer to by its name

Upvotes: 4

dude8019
dude8019

Reputation: 1

I made this function which could be used as follows to return the value you want.

Usage example: GetTableValue(yourTableName, "ID", 3, "header4")

Function GetTableValue( _
  ByRef table As ListObject, _
  ByVal columnNameForKeyValue As String, _
  ByVal keyValue As Variant, _
  ByVal columNameForReturnValue As String _
) As Variant

  Dim rowIndex As LongPtr
  Dim columnIndex As LongPtr

  On Error GoTo ErrorHandling

  rowIndex = Application.Match(keyValue, 
  table.ListColumns(columnNameForKeyValue).DataBodyRange, 0)

  columnIndex = table.ListColumns(columNameForReturnValue).Index

  GetTableValue = table.DataBodyRange.Cells(rowIndex, columnIndex).value

  Exit Function

  ErrorHandling:

  GetTableValue = "Error: Review inputs"

End Function

Upvotes: 0

Tom
Tom

Reputation: 41

If you want to write it short:

[MyTable[header4]].Rows(2)

Upvotes: 3

Julien Prévost Franco
Julien Prévost Franco

Reputation: 1

Much simpler, and dynamic:

ThisWorkbook.Worksheets("WorkSheet_Name").Range("Table_Name").Rows(Table_row_number).Select

Upvotes: 0

Mike Lasch
Mike Lasch

Reputation: 91

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.

Upvotes: 7

TomJohn
TomJohn

Reputation: 747

It seems to me that @Salam Morcos solution will not give a proper answer. If table starts from cell A2 statment [MyTable[FirstColumnName]].Column would give value of 2. Proper solution would be:

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

Upvotes: 4

All4Peace
All4Peace

Reputation: 319

A shorter answer is:

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

Much cleaner and easier!

Upvotes: 21

brettdj
brettdj

Reputation: 55682

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)

Upvotes: 56

Related Questions