Capturing Column as Alpha

Lisa928

Board Regular
Joined
Jun 13, 2002
Messages
173
I am writing code and need to capture the column my cursor is in, but as an ALPHA, not numeric. The only way I currently know how to is using 'currentcell = ActiveCell.Column' but it is numeric.

Can anyone help?
Thanks in advance for your time!
Lisa
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
If it's the first 26 columns, an easy way is
mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Activecell.Column,1)
 

Lisa928

Board Regular
Joined
Jun 13, 2002
Messages
173
That is a great tip, but unfortunately it is not. Any other suggestions??
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Hi,

Do you make references to columns AA and above? Is that why the MID option is not working for you? Bob's suggestion is clever.

There have been a few really nice UDFs to do this posted to the board a while ago. I can't think of the thread off hand, though. In any event, here is a quick routine which should do what you'd like. It can be shortened considerably, too.

Also, there are other approaches that work, so let us know if something else is needed.

<pre>Sub test()
Dim ColLetter As String
Dim ColNum As Integer


ColNum = ActiveCell.Column
ColLetter = Left(ActiveSheet.Cells(1, ColNum).Address(False, False), _
(ColNum <= 26) + 2)

MsgBox ColLetter
End Sub</pre>
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
This is the easiest 1-liner I can think of:

Left(ActiveCell.EntireColumn.Address(columnabsolute:=False) & "", Application.WorksheetFunction.Find(":", x1) - 1)
 

Forum statistics

Threads
1,144,765
Messages
5,726,173
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top