ActiveCell.Column returns # instead of a letter

[starfish]

I'm trying to use ActiveCell.Column to store a column letter in a variable to be used later.

Only trouble is that it is returning a number instead of a letter. So column C shows up as 3.

Is there a way to make the function return the letter?

I'm using this to store the column letter so that I can move to known rows within a variable column that need to be pasted to another sheet.

Thanks in advance for any help.

 

[dk]

Here's one example: -

http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm

 

[starfish]

Thanks so much Dan for your reply. I'll give that code a try.

 

[tusharm]

There is very little reason why one needs the letter corresponding to a column. In code, it is almost always easier to deal with a number, for example, Cells(row-number,column-number)

 

[NateO]

Howdy, this is essentially the same as Dan's linked variety, but looks shorter...<pre>
Function GetCol(ByVal n As Range)
If n.Column< 27 Then GetCol = Chr(64 + n.Column) Else _
GetCol = Chr(64 + n.Column 26) + Chr(64 + (n.Column Mod 26))
End Function</pre>

This particular function takes a range, e.g.,

=getcol(iv4)

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-12-16 20:32

 

[tusharm]

Ummm...not quite.

Modified version that works with cols ending in 'Z' (such as AZ, BZ, etc.):
<pre>Function GetCol(ByVal n As Range)
If n.Column <= 26 Then GetCol = Chr(64 + n.Column) Else _
GetCol = Chr(64 + (n.Column - 1) 26) + Chr(64 + 1 + ((n.Column - 1) Mod 26))
End Function
</pre>.

On 2002-12-16 20:24, NateO wrote:
Howdy, this is essentially the same as Dan's linked variety, but looks shorter...

<pre>
Function GetCol(ByVal n As Range)
If n.Column < 27 Then GetCol = Chr(64 + n.Column) Else _
GetCol = Chr(64 + n.Column 26) + Chr(64 + (n.Column Mod 26))
End Function</pre>

This particular function takes a range, e.g.,

=getcol(iv4)
{snip}

 

[NateO]

Whoops!

Nice catch TM!

 

[sirplus]

That didn't work for me for columns over AZ
But this did

Function GetCol(ByVal n As Range)
If n.Column <= 26 Then GetCol = Chr(64 + n.Column) Else _
GetCol = Chr(64 + (n.Column - 1 - (n.Column - 1) Mod 26) / 26) + Chr(64 + 1 + ((n.Column - 1) Mod 26))
End Function