How to use structured reference (ListColumns) in VBA

[Carl S.]

I want to do something simple...loop through a range retrieving values one at
time and taking action on that value. This range is an Excel 2007 table
column. Easy enough...BUT I want to use structured references to a table
column by NAME, not numbers. I don't want to define new names, just use the
table column headings. I've found ways of doing it with quotes, but that's
not structured....the names in quotes do not change if the table heading is
changed by the user.

The formulas change if the table column heading is changed, but I want it to
change in VBA too. Can someone post a simple example that I can paste that
will work? Thanks!

 

[Chip Pearson]

I don't really follow what you are attempting to do. Somewhere in all
this, there must be a literal constant against which a value can be
tested. The following might be what you're looking for, or at least
get you going in the right direction.

When you say that the column headings change, are all the names
preserved, just reordered, or are complete new names added
arbitrarily?

Suppose your data table is in cells C4:F8, where row 4 is the column
heading labels. The following code will scan down column C and if a 3
is found, it will return the data in that row in the column specified
by the FindHeader value. This value must be hard coded into the code.
How else, if not by number which you say you don't want to use, are
you going to identify the column from which the data is to be
retrieved. I suppose you could prompt the user for the column
heading. Replace

FindHeader = "research"

with

FindHeader = InputBox("Enter a column name")


Sub AAA()
Dim TableStart As Range
Dim HeaderValues As Range
Dim NumColumns As Long
Dim FindHeader As String
Dim HeaderN As Long
Dim R As Range
Dim V As Variant

Set TableStart = Range("C4")
Set HeaderValues = Range(TableStart, TableStart.End(xlToRight))
Set R = TableStart(2, 1)
FindHeader = "research"
Do Until R.Value = vbNullString
If R.Value = 3 Then
HeaderN = Application.Match(FindHeader, HeaderValues, 0)
V = R.Offset(0, HeaderN)
MsgBox "Found: " & CStr(V) & " in column '" & FindHeader &
"'."
Exit Do
End If
Set R = R(2, 1)
Loop
End Sub


Beyond this, I don't really understand what you are trying to do? How
do you identify the columns?

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Herbert Seidenberg]

Excel 2007 Tables
Example with ListObjects and ListColumns
Variable column headers.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_16_10.xlsm