Here's one example: -
http://www.rb-ad.dircon.co.uk/rob/ex...tips/index.htm
This is a discussion on ActiveCell.Column returns # instead of a letter within the Excel Questions forums, part of the Question Forums category; I'm trying to use ActiveCell.Column to store a column letter in a variable to be used later. Only trouble is ...
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.
Here's one example: -
http://www.rb-ad.dircon.co.uk/rob/ex...tips/index.htm
Thanks so much Dan for your reply. I'll give that code a try.
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)
Tushar Mehta (Microsoft MVP Excel 2000-present)
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office
Howdy, this is essentially the same as Dan's linked variety, but looks shorter...
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
This particular function takes a range, e.g.,
=getcol(iv4)
_________________
Cheers, Nate Oliver
[ This Message was edited by: nateo on 2002-12-16 20:32 ]
Ummm...not quite.
Modified version that works with cols ending in 'Z' (such as AZ, BZ, etc.):
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
On 2002-12-16 20:24, NateO wrote:
Howdy, this is essentially the same as Dan's linked variety, but looks shorter...
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
This particular function takes a range, e.g.,
=getcol(iv4)
{snip}
Tushar Mehta (Microsoft MVP Excel 2000-present)
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office
Whoops!
Nice catch TM!
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
Last edited by sirplus; Mar 15th, 2010 at 11:58 PM.
Like this thread? Share it with others