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" wrote in message
...
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" wrote in message ...
How to make the Range("B10").Column returns B(or B$) not 2 ?