Re: Example: how to convert a column letter to a number

  • Thread starter Thread starter Ron de Bruin
  • Start date Start date
R

Ron de Bruin

Hi Dan

Posted by Chip Pearson
******************

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

Temp = ColumnNumber("D") ' returns 4

To do the reverse, changing a number to a column letter, use

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber > 26))
End Function

Temp = ColumnLetter(26) ' returns "Z"
 
Thanks! Much easier than my way.

Ron de Bruin said:
Hi Dan

Posted by Chip Pearson
******************

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

Temp = ColumnNumber("D") ' returns 4

To do the reverse, changing a number to a column letter, use

Function ColumnLetter(ColNumber) As String
ColumnLetter = Left(Cells(1, ColNumber).Address(True, False), _
1 - (ColNumber > 26))
End Function

Temp = ColumnLetter(26) ' returns "Z"
Click to expand...
 
Back
Top