Hi, all,

I am writing some VBA code and want to control an Excel 2010
table-object and a column or headers to a column from it with set
abstraction names rather than just using the real name. I'm having
trouble getting it to work.

So I have a table called "Table2". I have a column called "Key" as
the left column of that.

Dim tblTgt As Range

Set tblTgt = [table2] 'workbook scope

Well, that actually works. But then I can't, later,
select a column such as "Key" or a headder row or cell.
It won't work:

Range(TblTgt[[#Headers],["Key"]]).Select
Range(TblTgt[[#Headers],[Key]]).Select
TblTgt[[#Headers],[Key]].Select
TblTgt[[#Headers],["Key"]].Select

and various other variations on that kind of thing won't
work. I have to go back to:

Range("Table2[[#Headers],[Key]]").Select

That works, but it defeats the point of the abstraction
in the code.

Ideas?

hi,

the first column address:
firtColumn = Range("Table2").Columns(1).Address

the last column address:
lastColumn = Range("Table2").Columns(Range("Table1").Columns.Co unt).Address

isabelle

Le 2015-04-14 19:19, Sarah H. a écrit :
Hi, all,

I am writing some VBA code and want to control an Excel 2010
table-object and a column or headers to a column from it with set
abstraction names rather than just using the real name. I'm having
trouble getting it to work.

So I have a table called "Table2". I have a column called "Key" as
the left column of that.

Dim tblTgt As Range

Set tblTgt = [table2] 'workbook scope

Well, that actually works. But then I can't, later,
select a column such as "Key" or a headder row or cell.
It won't work:

Range(TblTgt[[#Headers],["Key"]]).Select
Range(TblTgt[[#Headers],[Key]]).Select
TblTgt[[#Headers],[Key]].Select
TblTgt[[#Headers],["Key"]].Select

and various other variations on that kind of thing won't
work. I have to go back to:

Range("Table2[[#Headers],[Key]]").Select

That works, but it defeats the point of the abstraction
in the code.

Ideas?

oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

isabelle

anotherway,

x = Application.Match("Key", [table2].Rows(1), 0)
rangeKey = Range("Table2").Columns(x).Address

isabelle

Le 2015-04-14 23:15, isabelle a écrit :
oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

isabelle

isabelle wrote:
anotherway,

x = Application.Match("Key", [table2].Rows(1), 0)
rangeKey = Range("Table2").Columns(x).Address

isabelle

Le 2015-04-14 23:15, isabelle a écrit :
oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

Interesting ideas. But I wasn't clear about what I am trying to do.

Basically, I have a long set of VBA procedures. I want to set all
the real names to variable names near the top, so if I change my sheets
later I won't have to search all through all the code to find individual
instances of, e.g., "Table2". What If I rename Table2 next year and
call it "MyUpdatedTable"? Then, on lines 435 and 654 of my procedure(s)
somewhere it says "Table2", and the code won't work.

So at the top I wanted

set foo = Range("Table2")

If I change my tables later, I know to just go to the definitions
area of my macros and change the set variables there.

This seems to me to be careful and correct programming technique
for me and my possible successors who won't want to look at
spaghetti code to figure out what I was doing in Spring 2015.

But I can't figure out how to do it for individual columns in a table.

Well, let me amend that. I can do it, but only if I set each column
manually. I don't want that. I want the same flexibility to find
subranges using the abstraction name "foo" that I have with the
actual (current) name "Table2".

Isabelle, I appreciate your kind efforts of help.

May I mention, as an aside, that "v.org" is an actual domain? If
you are not connected with it, I would suggest you use a
legitimate fake address if you want to munge your address in
the group. That's why I choose "example.com": it was created
and reserved for such purposes and is sanctioned for that.
http://en.wikipedia.org/wiki/Example.com

If you do actually work at v.org, then I apologize. :-)

--
/s

Sarah H. wrote:
isabelle wrote:
anotherway,

x = Application.Match("Key", [table2].Rows(1), 0)
rangeKey = Range("Table2").Columns(x).Address

isabelle

Le 2015-04-14 23:15, isabelle a écrit :
oops, correction:

the last column address:
lastColumn = Range("Table2").Columns(Range("Table2").Columns.Co unt).Address

Interesting ideas. But I wasn't clear about what I am trying to do.

Isabelle, you were closer to right than I gave you credit for. Apologies!

This works (tested in the "Immediate" window):

set foo=[table2]
x=Application.Match("Key", foo.Rows(0), 0)
debug.Print x
1
foo.columns(x).select

So that's pretty much what I want to be able to do. Thanks!
The trick is, Row(0) is the table header. I wonder if this
is documented anywhere.

--
/s