return colmn number + index

[medicenpringles]

is there any way to return the LETTER name of a column? the .Column property returns the index, but is there a simple way of returning the A, B, and so on value of column? this is what i would like to do:

#################################

Dim myRange as Range
Dim rowCopy as Integer
Dim colCopy as Integer
Dim colCopyLetter as String
Dim NewRange as String

Set rowCopy = myRange.Row
Set colCopy = myRange.Column
Set colCopyLetter = ???
Set NewRange = ColCopyLetter & rowCopy

'some code

Set myRange = (NewRange)

##################################

any help?

[Don Guillett]

look here for ways
http://tinyurl.com/7fyl8

--
Don Guillett
SalesAid Software
[email protected]
"medicenpringles"
<[email protected]> wrote in
message news:[email protected]...
>
> is there any way to return the LETTER name of a column? the .Column
> property returns the index, but is there a simple way of returning the
> A, B, and so on value of column? this is what i would like to do:
>
>
> Dim myRange as Range
> Dim rowCopy as Integer
> Dim colCopy as Integer
> Dim colCopyLetter as String
> Dim NewRange as String
>
> Set rowCopy = myRange.Row
> Set colCopy = myRange.Column
> Set colCopyLetter = ???
> Set NewRange = ColCopyLetter & rowCopy
>
> 'some code
>
> Set myRange = (NewRange)
>
>
> --
> medicenpringles
>
>
> ------------------------------------------------------------------------
> medicenpringles's Profile:
http://www.excelforum.com/member.php...o&userid=16458
> View this thread: http://www.excelforum.com/showthread...hreadid=382337
>

[Vasant Nanavati]

Lots of errors here. Try:

Dim myRange as Range
Dim rowCopy as Long
Dim colCopy as Integer
Dim colCopyLetter as String
Dim NewRange as String

rowCopy = myRange.Row
colCopy = myRange.Column
colCopyLetter = Left(myRange(1, 1).Address(False, False), _
1 - CInt(myRange(1, 1).Column > 26))
NewRange = ColCopyLetter & rowCopy

'some code

Set myRange = Range(NewRange)

But honestly, I don't quite understand what you are trying to do.

--

Vasant


"medicenpringles"
<[email protected]> wrote in
message news:[email protected]...
>
> is there any way to return the LETTER name of a column? the .Column
> property returns the index, but is there a simple way of returning the
> A, B, and so on value of column? this is what i would like to do:
>
>
> Dim myRange as Range
> Dim rowCopy as Integer
> Dim colCopy as Integer
> Dim colCopyLetter as String
> Dim NewRange as String
>
> Set rowCopy = myRange.Row
> Set colCopy = myRange.Column
> Set colCopyLetter = ???
> Set NewRange = ColCopyLetter & rowCopy
>
> 'some code
>
> Set myRange = (NewRange)
>
>
> --
> medicenpringles
>
>
> ------------------------------------------------------------------------
> medicenpringles's Profile:
> http://www.excelforum.com/member.php...o&userid=16458
> View this thread: http://www.excelforum.com/showthread...hreadid=382337
>