How to relate column number (eg 1) to column Name (eg A) 2

[owentmoore]

Hi all,

For a specific task I am reading the column number of a value in my sheet as being in column 57 which is column name "BE". I need to use the column name in a spreadsheet to locate a formula I have, but I do not know how to turn the 57 into BE.

Can anyone help?

Thaks
Owen

 

[PHV]

intCol = 57
strCol = Split(Mid(Columns(intCol).Address, 2), ":")(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

You can just use Cells instead of Range.

Example:

Cells(2, 57).Activate

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[owentmoore]

Brilliant - thanks guys

 

[HughLerwill]

Sub MyMac()
For i = 1 To 26
Range(Chr$(i + 64) & 1) = "col " & Chr$(i + 64)
Next
End Sub

is good but needs more imagination after 26 columns...

regards Hugh

 

[PHV]

Another way working in any VBA (ie Excel not needed):
strCol = IIf(intCol > 26, Chr$(64 + intCol \ 26), "") & Chr$(64 + intCol Mod 26)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Thanks PH I'll remember that next time I want an AHhh! Or maybe just an AHh!

Its Friday!