How to reference named table column in excel VBA?

Question

The syntax for using the Item property with the Cells property is as follows:

Cells.Item(Row,Column)

You must use a numeric value for Row, but you may use the numeric value or string value for Column. Both of the following lines refer to cell C5:

Cells(5,"C")
Cells(5,3)

Because the Item property is the default property of the RANGE object, you can shorten these lines as follows:

Cells(5,"C")
Cells(5,3)

I recently discovered you can do this:

For Each cell In Range("A2:A3")
Cells(cell.Row,3)

How can I do this (I need the correct syntax)

For Each cell In Range("A2:A3")
Cells(cell.Row,cell.Row.NamedColumn"Customer")

Isaac Moses · Accepted Answer · 2015-01-29 14:23:21Z

3

You should select Column E and assign a range name to it of CUSTOMER. Then, you can refer to it in your code as Range("CUSTOMER") and to its column number as Range("CUSTOMER").Column. If you cut this column and paste it somewhere else, its name will come with it, so the references in your code will stay valid.

answered Jan 29, 2015 at 14:23

Isaac Moses

1,6196 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gallaxhar Over a year ago

This is the correct answer. I wish I could use table headers instead of manually named ranges, but this works.

basodre · Accepted Answer · 2015-01-29 14:12:58Z

2

One method is to search the header row for the Column name that you want to use. The returned Range object's column property indicates the column you'd want to use. See the below code and let us know if you need additional help.

Sub LocateByHeader()
    Dim rngFnder As Range
    Dim rngHeader As Range

    Const HEADER_ROW As Integer = 1

    Set rngHeader = Intersect(Sheet1.Rows(HEADER_ROW), Sheet1.UsedRange)

    Set rngFnder = rngHeader.Find("Customer")

    If rngFnder Is Nothing Then
        'Do some code in  case the header isn't there
    Else
        ' rngFnder.Column will give the index of the column with the customer name
    End If

End Sub

answered Jan 29, 2015 at 14:12

basodre

5,7901 gold badge18 silver badges25 bronze badges

2 Comments

Gallaxhar Over a year ago

this seems extremely complicated and I intend to use the solution constantly/repeatedly. there should be a simple syntax to do this in one line like Cells(cell.Row, Range("[Schedule.xlsm]Week1Sheet!CUSTOMER")).Value

basodre Over a year ago

Unfortunately, Excel doesn't store normal spreadsheet headers as you've mentioned. While my solution is a bit more complex, you can encapsulate it into a User-Defined Function, and call it with a single command whenever needed

Collectives™ on Stack Overflow

How to reference named table column in excel VBA?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related