Results 1 to 8 of 8

ActiveCell.Column returns # instead of a letter

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 ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Posts
    2

    Default

    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.

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

  3. #3
    New Member
    Join Date
    Dec 2002
    Posts
    2

    Default

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

  4. #4
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default

    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)

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    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 ]

  6. #6
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default

    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}

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Whoops!

    Nice catch TM!

  8. #8
    New Member
    Join Date
    Sep 2007
    Posts
    3

    Default Re: ActiveCell.Column returns # instead of a letter

    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 10:58 PM.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com