Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Column Heading (XL97;SR2)

    Is it possible to determine the column heading via code?

    Example:
    MsgBox Range("C1").Address returns $C$1

    All I want is "C" to be returned.

    Thanks,
    John

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

    Re: Column Heading (XL97;SR2)

    Try:

    <pre> MsgBox Left(ActiveSheet.Range("C1").Address(False, False), 1)
    </pre>

    Legare Coleman

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Column Heading (XL97;SR2)

    <P ID="edit" class=small>(Edited by jscher2000 on 07-Sep-02 13:17. Forgot to give a concrete example. Whoops.)</P>I vaguely think I once did something like this by getting a numeric value for the column and then using that as an index into the alphabet, as set forth in the basic ASCII character set. For example:

    <pre>Function LetterIs(intColumnNumber As Integer) As String
    Dim intLetter As Integer, intPrecedingLetter As Integer
    intLetter = ((intColumnNumber - 1) Mod 26) + 1
    intPrecedingLetter = Int((intColumnNumber - 1) / 26)
    If intPrecedingLetter > 0 Then
    LetterIs = Chr(intPrecedingLetter + 64)
    End If
    LetterIs = LetterIs & Chr(intLetter + 64)
    End Function</pre>

    In your example, you would change the code to this:

    MsgBox <font color=red>LetterIs(</font color=red>Range("C1").<font color=red>Column)</font color=red>

    It should work up to column ZZ, after which it will not longer work properly.

    The following procedures can be used to test the function:
    <pre>Sub PrintToImmediate(intSomeColumn As Integer)
    Debug.Print "Column " & CStr(intSomeColumn) & " = " & LetterIs(intSomeColumn) & _
    "; " & Range(LetterIs(intSomeColumn) & "1").Column & " = " & _
    Range(LetterIs(intSomeColumn) & "1").Address
    End Sub

    Sub TestLetterIs()
    'run this to test
    PrintToImmediate 1
    PrintToImmediate 13
    PrintToImmediate 26
    PrintToImmediate 27
    PrintToImmediate 30
    PrintToImmediate 52
    PrintToImmediate 53
    PrintToImmediate 79
    PrintToImmediate 104
    PrintToImmediate 105
    PrintToImmediate 150
    End Sub</pre>

    The Immediate window then should show this:

    Column 1 = A; 1 = $A$1
    Column 13 = M; 13 = $M$1
    Column 26 = Z; 26 = $Z$1
    Column 27 = AA; 27 = $AA$1
    Column 30 = AD; 30 = $AD$1
    Column 52 = AZ; 52 = $AZ$1
    Column 53 = BA; 53 = $BA$1
    Column 79 = CA; 79 = $CA$1
    Column 104 = CZ; 104 = $CZ$1
    Column 105 = DA; 105 = $DA$1
    Column 150 = ET; 150 = $ET$1

    Hope this helps.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Heading (XL97;SR2)

    Or even

    <pre>Dim i As Integer
    If ActiveSheet.Range("CC1").Column < 27 Then
    i = 1
    Else
    i = 2
    End If
    MsgBox Left(ActiveSheet.Range("CC1").Address(False, False), i)
    </pre>



    Peter

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

    Re: Column Heading (XL97;SR2)

    Yes, a more better more general solution.
    Legare Coleman

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Column Heading (XL97;SR2)

    Editited to remove duplicate property assignment (oCell.Address.Address ) A.C.
    Try <pre> Dim oCol As Range
    Set oCell = Range("C1")
    MsgBox Mid(oCell.Address, 2, InStr(2, oCell.Address, "$") - 2)

    <font color=red>'or</font color=red>

    MsgBox Left(oCell.Address(ColumnAbsolute:=False), _
    1 + (oCell.Columns.Column > 26) * -1)</pre>


    Andrew C

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Column Heading (XL97;SR2)

    Or more briefly :<pre>Dim oCell As Range
    Set oCell = [C1]
    MsgBox Left(oCell.Address(, 0), 1 + (oCell.Column > 26) * -1)
    </pre>

    Andrew C

Posting Permissions

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