Selects a letter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger B. - 26 Jan 2006 16:24 GMT

I need a macro that selects the letter out of a cell address without the $
sign.

Thanks for your help in advance.

Severn

Reply to this Message

HotRod - 26 Jan 2006 16:27 GMT

Example please?

>I need a macro that selects the letter out of a cell address without the $
>sign.
>
> Thanks for your help in advance.
>
> Severn

Reply to this Message

Ron de Bruin - 26 Jan 2006 16:39 GMT

Try this

Sub test()
If ActiveCell.Column > 26 Then
MsgBox Left(ActiveCell.Address(False, False), 2)
Else
MsgBox Left(ActiveCell.Address(False, False), 1)
End If
End Sub

Signature

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

>I need a macro that selects the letter out of a cell address without the $ sign.
>
> Thanks for your help in advance.
>
> Severn

Reply to this Message

Otto Moehrbach - 26 Jan 2006 16:44 GMT

This little macro does what you want.
Sub GetColLetter()
Dim c As Range
Set c = ActiveCell
MsgBox Left(c.Address(0, 0), 2 + (c.Column <= 26))
End Sub
HTH Otto

>I need a macro that selects the letter out of a cell address without the $
>sign.
>
> Thanks for your help in advance.
>
> Severn

Reply to this Message

Chip Pearson - 26 Jan 2006 16:55 GMT

That will work in any current version of Excel. However, it won't
work in the next version of Excel. For Excel 12 compatibility,
use

Dim Rng As Range
Dim ColLetter As String
Set Rng = ActiveCell
ColLetter = Split(Rng.Address(True, True, xlA1), "$")(1)
Debug.Print ColLetter

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

> This little macro does what you want.
> Sub GetColLetter()
[quoted text clipped - 9 lines]
>>
>> Severn

Reply to this Message

Otto Moehrbach - 26 Jan 2006 20:35 GMT

Chip
Do you have a source for info (differences) in Excel 12? Otto

> That will work in any current version of Excel. However, it won't work in
> the next version of Excel. For Excel 12 compatibility, use
[quoted text clipped - 18 lines]
>>>
>>> Severn

Reply to this Message

Ron de Bruin - 26 Jan 2006 20:40 GMT

This Blog is very good Otto
http://blogs.msdn.com/excel/default.aspx

Signature

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

> Chip
> Do you have a source for info (differences) in Excel 12? Otto
[quoted text clipped - 19 lines]
>>>>
>>>> Severn

Reply to this Message

Otto Moehrbach - 27 Jan 2006 02:39 GMT

Thanks Ron. Otto

> This Blog is very good Otto
> http://blogs.msdn.com/excel/default.aspx
[quoted text clipped - 23 lines]
>>>>>
>>>>> Severn

Reply to this Message

Toppers - 26 Jan 2006 16:51 GMT

One way:

CellAddr = Left(ActiveCell.Address(True, False), InStr(1,
ActiveCell.Address(True, False), "$") - 1)

> I need a macro that selects the letter out of a cell address without the $
> sign.
>
> Thanks for your help in advance.
>
> Severn

Reply to this Message