vba code to convert number into a column letter

[AlphaRisk]

I am using some code which currently converts a number into the corrisponding column letter. eg 1 = A, 2 = B, 27 = AA, 56 = BD etc.

Here is the code:

If mycolumn > 26 Then
Mcl = Chr(Int((mycolumn - 1) / 26) + 64) & Chr(Int((mycolumn - 1) Mod 26) + 65)
Else
Mcl = Chr(mycolumn + 64)
End If

Where "mycolumn" = number & "Mcl" = letter.

This code will only calculate correctly for numbers up to 702 as this is ZZ.
How can i add onto this code to calculate for 3 letter column. eg AAA = 703, 4206 = EFT.

I cant seem to get my head around it.

 

[VoG]

How to rewrite function so it can convert column letter to number

Try

Function ColumnNumber(AlphaColumn As String) As Long
ColumnNumber = Columns(AlphaColumn).Column
End Function

 

[tongcy]

I rewrite code as below, it works if AlphaColumn was start from A till AAA but failed if test with ZZZ, any idea why it failed ?

Sub LtrCol()
Dim AlphaColumn As String
Dim ColumnNumber As Long
AlphaColumn = "ZZZ"
ColumnNumber = Columns(AlphaColumn).Column
MsgBox ColumnNumber
End Sub

 

[Andrew Poulsom]

There isn't a column ZZZ in Excel.

 

[MARK858]

failed if test with ZZZ, any idea why it failed ?

last column in 2007+ is XFD so it fails because column ZZZ doesn't exist

Edit: seems like an echo of what Andrew stated now.

 

[tongcy]

How about last column in excel 2013 ? same as 2007 ?

 

[MARK858]

Yep, that is why I put + at the end of 2007

Excel specifications and limits - Excel - Office.com

Column 16384 is XFD

 

[tongcy]

Thanks a lot

 

[tongcy]

Anyone could help to rewrite below code so that in case if user enter column letter > XFD, a msgbox will pop up to notify user invalid value.

If StrColTextBox.Text <> "" Then
  If IsLetter(StrColTextBox.Text) Then
       If Len(StrColTextBox.Text) <= 3 And Columns(StrColTextBox.Text).Column <= 16384 Then
         StrColTextBox.Text = UCase(StrColTextBox.Text)
         UCase (StrColTextBox.Text)
         StrCol = StrColTextBox.Text
       Else
         MsgBox "Please enter a valid value.The valid value should between A to XFD ", vbCritical
         StrColTextBox.Text = ""
       End If
  Else
    MsgBox "Please enter a valid value.The valid value should between A to XFD ", vbCritical
    StrColTextBox.Text = ""
    Cancel = True
  End If
End If

 

[tongcy]

issues sorted by added error handling in the code.

 

[Scott Huish]

This isn't a valid line of code on its own:

UCase (StrColTextBox.Text)

I think you mean:

StrColTextBox.Text = UCase (StrColTextBox.Text)