Column number to letter

Philosophaie · Jul 13th 2015

I want to insert a simple formula into a spreadsheet except the rows and columns are all in numbers.

This is what I have:

Code

Sub display()
      For k = 5 to 35
          Range(Column(k) & "6") = k
      Next  k
 End Sub
 Function Column(Byval I As Integer)
      'convert number into column?
         Column Alpha
 End Function

jindon · Jul 13th 2015

Re: Column number to letter

why not

Code

Cells(6, k) = k

**Trebor76** · Jul 13th 2015

Re: Column number to letter

What was wrong with my solution here

**Kenneth Hobson** · Jul 13th 2015

Re: Column number to letter

One of these two methods will work too.

Code

'http://www.mrexcel.com/forum/showthread.php?t=338789
Sub test_ColumnLetter_ColumnNumber()
  MsgBox ColumnLetter(40), , "40"
  MsgBox ColumnNumber("AN"), , "AN"
  MsgBox ColumnLetterSNB(40), , "40"
End Sub


Function ColumnLetter(ColumnNum As Integer) As String
  ColumnLetter = Split(Cells(1, ColumnNum).Address, "$")(1)
End Function


Function ColumnNumber(sColumnLetter As String) As Integer
  ColumnNumber = Cells(1, sColumnLetter).Column
End Function


Function ColumnLetterSNB(ColumnNum As Integer) As String
  Dim s As String
  ' http://www.vbaexpress.com/forum/showthread.php?t=42695
  'MsgBox [substitute(address(5,5,4),5,"")]
  s = "Substitute(Address(" & ColumnNum & ", " & ColumnNum & ",4)," & ColumnNum & ","""")"
  ColumnLetterSNB = Evaluate(s)
End Function

Display More

Column number to letter

Participate now!

Share