How to make the Range("B10").Column returns B(or B$) not 2 ?
How to make the Range("B10").Column returns B(or B$) not 2 ?
One way is to use Address and strip out to column element......
ColumnAddress = Mid(ActiveCell.Address, 1, InStrRev(ActiveCell.Address,
"$") - 1)
--
Cheers
Nigel
"OKLover" <[email protected]> wrote in message
news:[email protected]...
> How to make the Range("B10").Column returns B(or B$) not 2 ?
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" <[email protected]> wrote in message news:[email protected]...
> How to make the Range("B10").Column returns B(or B$) not 2 ?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks