Is it possible to set up column heads for a named range and refer to them
programatically. For example.


Week ColStart ColEnd
1 4 19
2 1 16
3 2 18


Something like . . . . .

Range.cells( 1, Range.ColStart)

???

If you give the cells that contain the headers specific range names you
could refer to the other cells using the form

Dim CellVal as Long
CellVal = Cells(Range("Week").Row+1,Range("ColEnd").Column). value

In this case the +1 is the row offset for week 1

Or you could use "Offset" - by selecting the cell containing "Week" the
ColEnd for week 1 becomes

Dim CellVal as Long
Range("Week").Select
ActiveCell.Offset(1,2).Value

However be careful if you start to insert new rows or columns

Goofy wrote:
Is it possible to set up column heads for a named range and refer to them
programatically. For example.


Week ColStart ColEnd
1 4 19
2 1 16
3 2 18


Something like . . . . .

Range.cells( 1, Range.ColStart)

???

Thanks Alan,

I did think about this, and decided to use a Global Constant to refer to the
column offset numbers instead, its a bit clunky but will work provided I
remember to update them.

The reason I chose to do this was that I am able to refer to the value using
code completion which makes life easier than trying to remember the
RangeName.

As a .NET developer I am used to being able to refer to a typed dataset by
columnName which makes like so much easier and readable.

Thanks again for your input.





"Alan" wrote in message
ups.com...
If you give the cells that contain the headers specific range names you
could refer to the other cells using the form

Dim CellVal as Long
CellVal = Cells(Range("Week").Row+1,Range("ColEnd").Column). value

In this case the +1 is the row offset for week 1

Or you could use "Offset" - by selecting the cell containing "Week" the
ColEnd for week 1 becomes

Dim CellVal as Long
Range("Week").Select
ActiveCell.Offset(1,2).Value

However be careful if you start to insert new rows or columns

Goofy wrote:
Is it possible to set up column heads for a named range and refer to them
programatically. For example.


Week ColStart ColEnd
1 4 19
2 1 16
3 2 18


Something like . . . . .

Range.cells( 1, Range.ColStart)

???