PDA

View Full Version : Catering for columns that will move



Asterix
12-12-2007, 02:34 PM
Nothing to do with my post on macros and buttons...this one is more general. One thing I find annoying is coding based on an original idea for the sheet format and having to recode when I insert/ delete rows and columns.

Can I prevent this by referring to the title rows and columns of a spreadsheet as a range_name.

E.g. a column headed Total Points on the right of a championship table. If the same macro that works out the points every year is used but the number of events change (so the Total Points column will move left or right). Can I refer to the Total Points column header as the range name "Total_points" and use that as a reference in the code, so I don't have to adjust the cell references.

Thanks

figment
12-12-2007, 03:11 PM
you sould be able to use the names proporty of a workbook to find the correct group of cells, or, if the header stays in the same row you could scan the row looking for the right header, and use that to locat the corecet group of cells.

Bob Phillips
12-12-2007, 05:18 PM
Add defined names.

See http://www.xldynamic.com/source/xld.Names.html
Names In Excel - A Discussion