Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Returning a column letter (2000)

    I was more than surprised when the following statements returned the value 123, the column number of the relevant cell.

    Rows("1:1").Select
    Selection.Find("End column", LookIn:=xlValues).Activate
    LastFormulaColumn = ActiveCell.Column

    How can I get them to return the value "DS". I could try to code the rest of the program using activecell.offset. However this might be extremely difficult.

    Any ideas?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    Try:

    <pre> LastFormulaColumn = Left(ActiveCell.Address(False, False), Len(ActiveCell.Address(False, False)) - 1)
    </pre>


    The above will work as long as the row number is less than 10. If the row number can be greater than 10, the above has to be modified.

    It is also always best not to select cells on the worksheet unless it is absolutely necessary. The following will work for any row, and does not select any of the cells:

    <pre> LastFormulaColumn = Rows("1:1").Find("End column", LookIn:=xlValues).Address(False, False)
    Do While IsNumeric(Right(LastFormulaColumn, 1))
    LastFormulaColumn = Left(LastFormulaColumn, Len(LastFormulaColumn) - 1)
    Loop
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    Alex

    Try: LastFormulaColumn = Left(ActiveCell.Address,3)

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    Dang... Legare beat me to it.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    I think you meant:

    <pre>LastFormulaColumn = Left(ActiveCell.Address,2)
    </pre>


    However, that also will not work if the column is a single letter.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    Legare

    Thanks for this very neat bit of code.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    Legare,

    True, but I was working under the assumption that he was looking only at row one / column DS.

    The reason why I used a length of 3 was that my addy returned an absolute referernce of $DS$1

  8. #8
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Returning a column letter (2000)

    Function GetColumnLetters(ByVal ColumnNum As Long) As String
    On Error GoTo NoColumn
    Dim ColChars As String
    ColChars = Columns(ColumnNum).Address(False, False)
    GetColumnLetters = Left$(ColChars, 2 + CBool(ColumnNum < 27)) '2 + 0 or 2 + (-1)
    Exit Function

    NoColumn:
    Beep
    GetColumnLetters = vbNullString
    End Function

    Regards,
    Jim Cone
    San Francisco, CA

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    Well, if he was working on only row 1 / column DS, then all he would have to do is use "DS". <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    Yes, if you don't tell it differently, the Address property does return an absolute address. In that case, it would probably be better to use:

    <pre> LastFormulaColumn = Mid(ActiveCell.Address,2,2)
    </pre>

    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning a column letter (2000)

    Thanks Legare.
    I learn something new or see multiple ways to solve a problem each time I read these boards.

    I also kant spel reference very well

Posting Permissions

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