+ Reply to Thread
Results 1 to 3 of 3

The column return value?????

  1. #1
    OKLover
    Guest

    The column return value?????

    How to make the Range("B10").Column returns B(or B$) not 2 ?

  2. #2
    Nigel
    Guest

    Re: The column return value?????

    One way is to use Address and strip out to column element......

    ColumnAddress = Mid(ActiveCell.Address, 1, InStrRev(ActiveCell.Address,
    "$") - 1)


    --
    Cheers
    Nigel



    "OKLover" <OKLover@discussions.microsoft.com> wrote in message
    news:AB1AAC8B-DC52-464B-9545-CDCEEA40EA28@microsoft.com...
    > How to make the Range("B10").Column returns B(or B$) not 2 ?




  3. #3
    Ron de Bruin
    Guest

    Re: The column return value?????


    Try this

    Dim ColumnLetter As String
    Dim Col As Long
    Col = Range("B10").Column
    ColumnLetter = Left(Cells(1, Col).Address(True, False), 1 - (Col > 26))
    MsgBox ColumnLetter


    Or functions

    'Chip Pearson

    Function ColumnNumber(ColLetter) As Integer
    ColumnNumber = Cells(1, ColLetter).Column
    End Function

    Temp = ColumnNumber("D") ' returns 4

    To do the reverse, changing a number to a column letter, use

    Function ColumnLetter(ColNumber) As String
    ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
    1 - (ColNumber > 26))
    End Function

    Temp = ColumnLetter(26) ' returns "Z"




    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "OKLover" <OKLover@discussions.microsoft.com> wrote in message news:AB1AAC8B-DC52-464B-9545-CDCEEA40EA28@microsoft.com...
    > How to make the Range("B10").Column returns B(or B$) not 2 ?




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1