Hi Chris

You can certainly create range objects without column letters such as:

Cells(1,1).Value = 500

would assign the value 500 to A1 on the activesheet for example. Here,
the (1,1) refers to (Row Reference, Column Reference), so Cells(10,5)
refers to E10 for example.

If you want to create ranges over more than one cell, then you can use:

Range(Cells(1,1),Cells(10,5))

which refers to A1:E10. Using this syntax, you never have to worry
about what the column letter is.

You can use this kind of reference to return the column letter:

sColumnLetter =
left(cells(10,5).address(true,false),instr(1,cells (10,5).address(true,false),"$")-1)

Hope this helps!

Richard


Chrisso wrote:

I am reasonably new to VB programming for Excel so I must be missing
something - hopefully you can help.

I use a lot of Range objects and I construct them with cell references
i.e. Range("A1"). I tend to create Range objects using the column from
another Range object

It seems I can only get a column *number* from Range objects
(myRange.Column) . This means I have to use a sub-routine I found on
this newsgroup to translate this number to a column *letter* which is
what I need to create another Range object.

I also need column letters for feedback to the user as they deal in
column letters and *not* numbers.

So everytime I work on a new project I have to carry this column number
to column letter conversion subroutine with me - this does not seem
right! There must be some part of the language that will do this for me
but I have not found it.

What am I missing? Am I using Range objects incorrectly? Is there a way
to construct Range objects with column numbers?

Cheers for any ideas.

Chris