Finding Column Name in EXCEL

[Ham]

I have searched a spreadsheet and found the cell that I want. Now I have to build a formula using that cell. How can I determine the column name of the cell?

Thanks for the help. -- Ham

 

[SkipVought]

Hi,

With MyCell
   iCol = .Column
   lRow = .Row
End With

gives you the column/row number of MyRange (top-left of the range).

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Ham]

Skip - Thanks for the reply. I've got the column number, but I need the name (A, B, C, etc.). What I'm doing now is using 'icol mod 26' and an alpha string ('ABCDEF...Z') to generate the name, but there must be an easier way. What I need to do is reference the found column in a formula.

Thanks. -- Ham

 

[SkipVought]

strCol = Chr(MyCell.Column + 64) Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

BUT...
that will only work for the first 26 columns.

Or you can do the address and split out the column

vVal = Split(ActiveCell.Address, "$")
MsgBox vVal(1)

This WILL work for ANY column.

Skip,
metzgsk@voughtaircraft.com

 

[Ham]

Skip - Thanks for the help. That does look a lot easier.
-- Ham

 

[SkipVought]

Good!

But what are you doing with an alpha column value? Skip,
metzgsk@voughtaircraft.com

 

[Ham]

Skip - I'm referencing that cell in a formula that's stored in another cell. -- Ham

 

[SkipVought]

Man, use Cells(RowNbr, ColNbr) and if you need the address, then
Cells(RowNbr, ColNbr).Address. Don't mess with alpha!!! Skip,
metzgsk@voughtaircraft.com