Syntax for using table and column names

[James O. Thompson]

I can't figure out the syntax to use table and column names.

Assume I have a worksheet named "ClubMembers" containing a table named
"Members" with columns "Name" and "Number". Here is a simplified snippet
illustrating a dumb way to refer to them. How do I use table and column
names more elegantly?

Dim oListRow As ListRow
For Each oListRow In _
Worksheets("ClubMembers").ListObjects("Members").ListRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,

Jim Thompson

 

[Jim Cone]

Depends on what you are trying to do. Maybe...
'--
Sub EOQ()
ActiveSheet.ListObjects(1).Range.Columns(1).Value = "No Name"
ActiveSheet.ListObjects(1).Range.Columns(2).Value = 0
End Sub
'--
Curious... did you publish the list in order to name it, use code or something else?
--
Jim Cone
Portland, Oregon USA



"James O. Thompson"
<[email protected]>
wrote in message
I can't figure out the syntax to use table and column names.

Assume I have a worksheet named "ClubMembers" containing a table named
"Members" with columns "Name" and "Number". Here is a simplified snippet
illustrating a dumb way to refer to them. How do I use table and column
names more elegantly?

Dim oListRow As ListRow
For Each oListRow In _
Worksheets("ClubMembers").ListObjects("Members").ListRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,
Jim Thompson

 

[James O. Thompson]

I was wanting to use Column Names instead of absolute numbers so that future
modifications to the structure of the table would not cause the code to
fail. In the example you gave me I know I could use the table name such as:

ActiveSheet.ListObjects("Members").Range.Columns(1).Value = "No Name"

But, for example, if later mods swapped the order of the columns the code
would fail since they are absolute. The following kind of illustrates what
I want to happen but gives a syntax error on compilation:

ActiveSheet.ListObjects("Members").Range.Columns("Name").Value = "No Name"

Thanks for replying,

Jim Thompson

P.S. I didn't understand your question about my reasons for "publishing"
the list.

 

[Jim Cone]

Why don't you just name the data in each column of the list, something like...
"NumCol" and "NameCol" (excluding the headers, if any).
When data is added to the list the named range will automatically expand.
Same if the columns are moved.
So Range("NameCol").Value = "Sludge" will work in most cases.

I don't know how you are planning on rearranging the data or what
SharePoint might or might not do to the named ranges so you will have
test/experiment.
'--
My question was poorly put. I just want to know how you named the List?
--
Jim Cone
Portland, Oregon USA



I was wanting to use Column Names instead of absolute numbers so that future
modifications to the structure of the table would not cause the code to
fail. In the example you gave me I know I could use the table name such as:

ActiveSheet.ListObjects("Members").Range.Columns(1).Value = "No Name"

But, for example, if later mods swapped the order of the columns the code
would fail since they are absolute. The following kind of illustrates what
I want to happen but gives a syntax error on compilation:

ActiveSheet.ListObjects("Members").Range.Columns("Name").Value = "No Name"

Thanks for replying,

Jim Thompson

P.S. I didn't understand your question about my reasons for "publishing"
the list.

 

[James O. Thompson]

The example you gave doesn't appear to set one cell in the column "NameCol"
to "Sludge". It would seem to set every cell in the entire column.
Apparently I'm not expressing the problem clearly. Let me start from
scratch:

Suppose I have a worksheet named "Club".
Suppose that worksheet has a table named "Members".
Suppose that table has a column with a header row label of "Lastname".

What Excel 2007 VBA statement(s) would I use to set the 5th (row 5) members
last name to "Thompson".

Thanks for taking time to work with me,

Jim Thompson

 

[Patrick Molloy]

With Worksheets("Sludge")
.Range("Members").Cells(5,
Application.WorksheetFunction.Match("LastName", .Range("Members").Resize(1),
False)) = "Thomson"
End With

 

[Jim Thompson]

I've found what I was looking for. To set the 5th members "LastName"in the
"Members" table to "Thompson" would look like:

Range("Members[LastName]").Cells(5,1) = "Thompson"

Thanks everyone for your help.

Jim Thompson