how to extract column letter of a cell & select cell by referencing its column number

[luv2glyd]

If I have a named cell in the worksheet, "myCell", how can I extract it's column letter using VB?

Also, in addition to the above, if I have a column number (not letter) and a row number of a cell, how can I use that info to select that cell in my worksheet? So for example, I want to be able to select cell C5, (and all my sub knows is the cell's row, which is 5, and column number which is 3).

Thank you.

[pike]

Hi luv2glyd
use "Range("myCell").Column"

Please Login or Register  to view this content.

Cells( Row number, column Number)

a1 is cells (1,1)

[luv2glyd]

Thank you Pike. That answers my second quesiton. On occasion, I need to use actual column letter in my code vs. column number (for example: letter of cell ADB4 is "ADB"). How can I extract that info?

Thank you

[pike]

Hi luv2glyd

what are you trying to do?
you can use
cells(4,"ADB")

but where is ADB4 comming from?

[luv2glyd]

I can use column numbers most of the time, but someitmes when i work with a lot of data (which spans way to the right in the worksheet), it'd be nice to use column letters, so when I look at the code and the data location in workbook, its easier to check the code and the location of data for consistency, to make sure everything is alligned and working correctly. So rather then figuring out what the column number of a cell is (in example below column number of cell DK is 115), it'd be nice to reference that cell by column letter. Here's just one of the examples of the code I have:

Please Login or Register  to view this content.

So for the example above, I'd like to be able to use data_start_column in place of "DK" and extract this column letter in a way similar to:

Please Login or Register  to view this content.

[luv2glyd]

Also, many times the way I write code, for simplicity sake, is by recording a macro, and then going in and modifying it to do what I need it to do. Excel records everything with references to column letters vs. column numbers, so when I go in to tweak something, it'd be nice to know how to get the column letter, and then just insert it in the macro

[Marcol]

Try these functions

Please Login or Register  to view this content.

Call in VBa

Please Login or Register  to view this content.

Or as a UDF in Excel
=ColumnLetter(A1)

Please Login or Register  to view this content.

Call in VBa

Please Login or Register  to view this content.

Hope this helps

[pike]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[snb]

Use Excel's built-in facilities:

Please Login or Register  to view this content.

[Marcol]

And if you want to complete the set....

Please Login or Register  to view this content.

Enter in Excel as
=ColumnName(A1)

[luv2glyd]

Great! Thank you every one. I think I like the last two solutions the best - short and sweet to get what I need. Sorry for the late reply - was gone yesterday.