Convert Column Number to Alphabet

[arpanpanchal]

Hi Friends

I am using the below code to identify column number. However, it comes up with number and I want that to be in Alphabet (like column P etc).

Can you please help how should I amend the below code?

Sub Column()

Dim FindCol As Range

Set FindCol = Cells.Find(What:="May 16", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

MsgBox FindCol.Column

End Sub

Thanks in advance

[Trebor76]

Hi arpanpanchal,

You can use the strFindCol variable from this:

Option Explicit
Sub MyColumn()

Dim rngFindCol As Range
Dim strFindCol As String

Set rngFindCol = Cells.Find(What:="May 16", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
 :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
 False, SearchFormat:=False)

MsgBox rngFindCol.Column

strFindCol = Left(Cells(rngFindCol.Row, rngFindCol.Column).Address(True, False), Application.WorksheetFunction.Search("$", Cells(rngFindCol.Row, rngFindCol.Column).Address(True, False)) - 1)

MsgBox strFindCol

End Sub

Note it will fail if no match is found.

Please make sure you wrap your code with the appropriate tags as I have done.

Regards,

Robert

[millz]

Another way:

Sub Column()

Dim FindCol As Range

Set FindCol = Cells.Find(What:="May 16", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

MsgBox Replace(FindCol.Address(0, 0), FindCol.row, "")

End Sub

[bakerman2]

And another one.

Sub MyColumn()

Dim rngFindCol As Range

Set rngFindCol = Cells.Find(What:="May 16", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
 :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
 False, SearchFormat:=False)

MsgBox Split(rngFindCol.Address, "$")(1)

End Sub

[arpanpanchal]

Thanks a lot friends...It works perfect

Thanks again