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").L istRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,

Jim Thompson

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"

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").L istRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,
Jim 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" wrote in message
...
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"

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").L istRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,
Jim Thompson

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



"James O. Thompson" wrote in message ...
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" wrote in message
...
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"

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").L istRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,
Jim 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

"Jim Cone" wrote in message
...
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



"James O. Thompson" wrote in message
...
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" wrote in message
...
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"

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").L istRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,
Jim Thompson

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

"James O. Thompson" wrote in message
...
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

"Jim Cone" wrote in message
...
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



"James O. Thompson" wrote in message
...
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" wrote in message
...
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"

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").L istRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,
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


"Patrick Molloy" wrote in message
...

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

"James O. Thompson" wrote in message
...
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

"Jim Cone" wrote in message
...
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



"James O. Thompson" wrote in message
...
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" wrote in message
...
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"

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").L istRows
oListRow.Range.Cells(1, 1) = "No Name"
oListRow.Range.Cells(1, 2) = 0
Next oListRow

Thanks for your help,
Jim Thompson