CODEWITHSUNDEEP
excelvba
bo-oz
bo-oz

Reputation: 2872

How to refer to a column in a Range by it's name

I got a table in Excel to which I add a row. I then reference that row and am able to store values in the columns, by using numbers.

Dim table As ListObject
Dim lastRow As Range

Set table = sheet.ListObjects.Item("TagTable")
table.ListRows.Add
Set lastRow = table.ListRows(table.ListRows.Count).Range
lastRow(1) = "Value for column 1"
lastRow(2) = "Value for 2"

Problem with this is that it isn't very flexible. I'd rather address the column name. I am searching like crazy, but I can't find any info on how to achieve this. Is this even possible or am I doing something crazy with this code?

Upvotes: 2

Views: 8293

Answers (1)

chris neilsen
chris neilsen

Reputation: 53135

A ListObject has a ListColumns property, which can access columns by name.

One way to use it is:

Sub Demo()
    Dim ws As Worksheet
    Dim table As ListObject
    Dim lstrow As ListRow

    Set ws = ActiveSheet ' Set to your required sheet
    Set table = ws.ListObjects.Item("TagTable")
    Set lstrow = table.ListRows.Add
    lstrow.Range.Cells(1, table.ListColumns("NameOfColumn1").Index) = "Value for column 1"
    lstrow.Range.Cells(1, table.ListColumns("NameOfColumn2").Index) = "Value for 2"
End Sub

Upvotes: 5

Related Questions