Consulting

Page 3 of 3 FirstFirst 1 2 3
Results 41 to 49 of 49

Thread: Get Column Letter

  1. #41
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by firefytr
    No Ken, the VB version is still 6, you must only do a version check. Excel 97 was still based on VB5, hence the compilation check.
    I realize that. The question really was "is there any way we can create a conditional for 2007". I didn't think testing on version would work, but then I haven't played with conditional compliation much.

    Quote Originally Posted by firefytr
    And as sad as it is about FileSearch, I must abandon that as well. I prefer to use code that is compatible in many versions. I hate narrowing the margin.
    I'll tell you... if I had to convert a huge number of files to work with 2007, I'd do it. Then go back and modify them later... if I felt I needed to. As Bob had said (or eluded to) before, I don't see why it's any less valid a solution though.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  2. #42
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    No, I'm not saying it is not a valid solution, just not one that I would ever use in this specific instance. As for creating a [compilation] conditional for 2007, like I said, we cannot check the VB version, so we wouldn't be able to check it like that. The [basically] only hook we have is the version number. Especially since MSFT decided to not update VB anymore (as of yet), or any other emulated OM.

  3. #43
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by firefytr
    No Bob, you misunderstand. If I had a need for the Split() function in 97, I would think about the conditionally compiled UDF, of course. For something such as this [thread], no, I would never use it.
    But it was YOU that raised 97 in the first place!

  4. #44
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by firefytr
    No, I'm not saying it is not a valid solution, just not one that I would ever use in this specific instance.
    No, you said that you would NEVER use it.

    Quote Originally Posted by firefytr
    ... Im not arguing to argue, I don't like Bob's solution very much and I would never use it. Conditional or not ...

  5. #45
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by firefytr
    ... Im not arguing to argue...


    Yes, you are.

    (sorry, couldn't help myself)
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  6. #46
    VBAX Contributor
    Joined
    Jul 2004
    Location
    Gurgaon, India
    Posts
    148
    Location
    [vba]MsgBox Split((ActiveCell.Address(1, 0)), "$")(0)[/vba]

  7. #47
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Quote Originally Posted by xld
    No, you said that you would NEVER use it.
    And I amended it to say I would never use it in this specific instance, to clear up any confusion.

    Quote Originally Posted by Ken Puls
    Yes, you are.
    I'll take that as a compliment from you, Ken.

  8. #48

    Wink

    Quote Originally Posted by Tommy
    This is what I use when I need a column letter:
    [vba]
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    GiveColLeter Target.Column
    End Sub
    Public Function GiveColLeter(iNumber As Long) As String
    Dim A As String, B As String
    If iNumber \ 26 > 0 Then A = Chr(64 + iNumber \ 26)
    B = Chr(64 + ((iNumber / 26) - (iNumber \ 26)) * 26)
    GiveColLeter = A & B
    End Function
    [/vba]

    a few more pennies on the stack
    Hi Tommy,

    Tried to use your code and realized that it can't do column 26 (Z). Instead it returns A@.
    Modified your code:
    [vba]Public Function GiveColLetter(iNumber As Long) As String
    Dim A As String, B As String
    If iNumber > 26 Then
    A = Chr(64 + iNumber \ 26)
    B = Chr(64 + (iNumber - 26))
    Else
    B = Chr(64 + iNumber)
    End If
    GiveColLetter = A & B
    End Function[/vba]

    Regards.

  9. #49
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    As posted earlier (the one that got us into this mess!). Does column 27 on nicely.

    [vba]

    '-----------------------------------------------------------------
    Function ColumnLetter(Col As Long)
    '-----------------------------------------------------------------
    Dim sColumn As String
    On Error Resume Next
    sColumn = Split(Columns(Col).Address(, False), ":")(1)
    On Error Goto 0
    ColumnLetter = sColumn
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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