How do you do this?

[Flemming]

Hi all,

I'm using Excel 2007 and is thinging about what is the best pratice. The
issue is: "Finding the column number of a column and allowing the user to
add new columns"

Have a table in sheet1 - named tblProducts
This table have several columns ie. ID and Description

Normaly I have named the table headings like "fldProdId" and "fldProdDesc"
and created a Public Const gsRNG_PROD_ID As String = "fldProdID" and then
used the range property to find the Column no of the named range...

But now I found that I can use "tblProducts[[#Headers],[Id]]" and it will
work even when users insert new columns, BUUUT when the user changes the
Heading "Id" to "Id no" than I got problems.

How do you handle this situation best, most flexible and secure?
Can this "tblProducts[[#Headers],[Id]]" be used in any way or is the naming
of ranges and constants the proper way?

Questions - please ask.

Cheers,
Flemming

 

[Don Guillett]

Think about using the MATCH function to find the column number

 

[Flemming]

Thanks but MATCH will not work if the user changes the column name which he
is alowed to do - otherwise the Range("tblProducts[[#Headers],[Id]]").Column
would work fine.

Think about using the MATCH function to find the column number

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi all,

I'm using Excel 2007 and is thinging about what is the best pratice. The
issue is: "Finding the column number of a column and allowing the user to
add new columns"

Have a table in sheet1 - named tblProducts
This table have several columns ie. ID and Description

Normaly I have named the table headings like "fldProdId" and
"fldProdDesc" and created a Public Const gsRNG_PROD_ID As String =
"fldProdID" and then used the range property to find the Column no of the
named range...

But now I found that I can use "tblProducts[[#Headers],[Id]]" and it will
work even when users insert new columns, BUUUT when the user changes the
Heading "Id" to "Id no" than I got problems.

How do you handle this situation best, most flexible and secure?
Can this "tblProducts[[#Headers],[Id]]" be used in any way or is the
naming of ranges and constants the proper way?

Questions - please ask.

Cheers,
Flemming